XL2000: Macro Example to Return Item from a Worksheet Control

In Microsoft Excel, you can place a control, such as a list box or a combo 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 Sub procedure (macro) that takes the item that is chosen from a combo box list on a worksheet and places that item in the active cell of the worksheet.

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 a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104 (https://partner.microsoft.com/global/30000104)

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice (http://support.microsoft.com/gp/advisoryservice)

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS (http://support.microsoft.com/default.aspx?scid=fh;en-us;cntactms) To create a macro to return an item chosen from a combo box control on a worksheet to the active cell on the worksheet, use the steps in the following example:

1. Start Microsoft Excel and open a new workbook.
2. On the View menu, point to Toolbars, and then click Forms.
3. Click Combo Box on the Forms toolbar, and then draw the control on the worksheet.
4. While the combo box is still selected, click Control on the Format menu.
5. In the Format Control dialog box, click the Control tab.
6. In the Input range box, type E1:E5, and then click OK.
7. In the Name Box (at the left end of the Formula bar), type my control, and then press ENTER.
8. Enter the following data on Sheet1:
   E1: One
   E2: Two
   E3: Three
   E4: Four
   E5: Five
9. Press ALT+F11 to start the Visual Basic editor.
10. On the Insert menu, click Module.
11. In the module sheet, enter the following code:

    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
    					

12. Press ALT+F11 to return to Excel.
13. On Sheet1, right-click the combo box that you inserted, and then click Assign Macro on the shortcut menu.
14. In the Assign Macro dialog box, click Control_on_Worksheet in the Macro name list, and then click OK.
15. Select cell F1 (or any cell that is part of the input range of the control).
16. Click the arrow in the inserted combo box, and then click any item in the list that appears.
    
    The item you selected from the list appears in cell F1.

For more information about about adding controls to a worksheet, click Microsoft Excel Help on the Help menu, type controls in the Office Assistant or the Answer Wizard, and then click Search to view the topic.