Macro Example to Return Item from Worksheet Control

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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, 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, 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, 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, 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.