Microsoft KB Archive/138823

= Macro Example to Return Item from Worksheet Control =

Article ID: 138823

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q138823





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. 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
 On the File menu, click New. Click the View menu, point to Toolbars, and then click Forms. Click Combo Box on the Forms toolbar and draw the control on the worksheet. While the drop-down is still selected, click Control on the Format menu. 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. 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.</li> Enter the following on Sheet1:

E1: One

E2: Two

E3: Three

E4: Four

E5: Five

</li></ol>

To Create the Control in Earlier Versions of Excel
<ol> On the File menu, click New.</li> On the View menu, click Toolbars. In the Toolbars dialog box, click Forms, and then click OK.</li> 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.</li> On the Format menu, click Object.</li> 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.</li> 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.</li> Enter the following on Sheet1:

E1: One

E2: Two

E3: Three

E4: Four

E5: Five

</li></ol>

To Create the Macro (All Versions of Excel)
<ol> Insert a module sheet in your new workbook.</li>  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 </li> On Sheet1, right-click the drop-down, and then click Assign Macro on the shortcut menu.</li> In the Assign Macro dialog box, click Control_on_Worksheet in the list of macros, and then click OK.</li> 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).</li> 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.</li></ol>

<div class="references_section">