XL: Macro Example to Return Item from Worksheet Control

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0a, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for the Macintosh, version 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

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 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 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/default.asp

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

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