XL: Macro Example to Return Item from Worksheet Control

ID: Q138823

The information in this article applies to:

SUMMARY

In Microsoft Excel, you can place a control, such as a list box or a drop- down box, on a worksheet. You can also attach macros to these controls so that the macro runs when an item is selected from that control.

This article contains a sample Microsoft Visual Basic for Applications macro (Sub procedure) that takes the item that is chosen from a drop-down list on a worksheet and places that item in the active cell of the worksheet.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/
The following steps and Visual Basic code provide an example of how to return the item chosen from a drop-down control on a worksheet to the active cell on the worksheet.

To Create the Control in Excel 97 and Excel 98 Macintosh Edition

1. On the File menu, click New.

2. Click the View menu, point to Toolbars, and then click Forms.

3. Click Combo Box on the Forms toolbar and draw the control on the 

   worksheet.
4. While the drop-down is still selected, click Control on the Format menu.

5. In the Format Object/Format Control dialog box, click the Control tab. 

   In the Input Range box, enter the range "E1:E5" (without the quotation
   marks), and then click OK.
6. In the name box (at the left end of the formula bar), change the default 
   name of the drop-down to "my control" (without the quotation marks), and
   then press ENTER.
7. Enter the following on Sheet1: 

      E1: One
      E2: Two
      E3: Three
      E4: Four
      E5: Five

To Create the Control in Earlier Versions of Excel

1. On the File menu, click New.

2. On the View menu, click Toolbars. In the Toolbars dialog box, click 

   Forms, and then click OK.
3. On the Forms toolbar, click the Drop-Down button, and then click the 
   sheet until the drop-down is the size and shape you want.
4. On the Format menu, click Object.

5. In the Format Object/Format Control dialog box, click the Control tab. 

   In the Input Range box, enter the range "E1:E5" (without the quotation
   marks), and then click OK.
6. In the name box (at the left end of the formula bar), change the default 
   name of the drop-down to "my control" (without the quotation marks), and
   then press ENTER.
7. Enter the following on Sheet1: 

      E1: One
      E2: Two
      E3: Three
      E4: Four
      E5: Five

To Create the Macro (All Versions of Excel)

1. Insert a module sheet in your new workbook.

2. Enter the following macro code into the module sheet: 

      Sub Control_on_Worksheet()

      Dim mypick As Variant

         With Worksheets("Sheet1").DropDowns("my control")

            ' Set the value of mypick to the index number
            ' of the item chosen in the drop-down.

            mypick = .ListIndex

            ' Extract the actual item and put it into
            ' the active cell on the worksheet.

            ActiveCell.Value = .List(mypick)

            ' Empty out the drop-down.
            .Value = 0

         End With

      End Sub
3. On Sheet1, right-click the drop-down, and then click Assign Macro on the 

   shortcut menu.
4. In the Assign Macro dialog box, click Control_on_Worksheet in the list 
   of macros, and then click OK.
5. Select a cell on the worksheet where you would like the item you 
   selected to appear.

   Note that you should not select the cells that make up the input range

   for the drop-down box (E1:E5 in this example).
6. Click the drop-down, and then click any item in the drop-down list. 

   You should see the item you selected from the list appear in the active
   cell.

REFERENCES

For more information about adding controls to a worksheet, click the Index tab in Microsoft Excel 7.0/97 Help, type the following text 

   Forms
and then double-click the selected text to go to the "Adding controls to a sheet" topic.

"Visual Basic User's Guide," version 5.0, Chapter 11, "Controls and Dialog Boxes"

Additional query words: 5.00 7.00 8.00 XL97 XL98 dropdown drop down 

Keywords          : kbprg kbdta kbdtacode PgmHowto KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999