Microsoft KB Archive/213381

= XL2000: Macro Example to Return Item from a Worksheet Control =

Article ID: 213381

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213381





SUMMARY
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.



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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

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

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:  Start Microsoft Excel and open a new workbook. On the View menu, point to Toolbars, and then click Forms. Click Combo Box on the Forms toolbar, and then draw the control on the worksheet. While the combo box is still selected, click Control on the Format menu. In the Format Control dialog box, click the Control tab. In the Input range box, type E1:E5, and then click OK.</li> In the Name Box (at the left end of the Formula bar), type my control, and then press ENTER.</li> Enter the following data on Sheet1:

E1: One

E2: Two

E3: Three

E4: Four

E5: Five

</li> Press ALT+F11 to start the Visual Basic editor.</li> On the Insert menu, click Module.</li>  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 </li> Press ALT+F11 to return to Excel.</li> On Sheet1, right-click the combo box that you inserted, and then click Assign Macro on the shortcut menu.</li> In the Assign Macro dialog box, click Control_on_Worksheet in the Macro name list, and then click OK.</li> Select cell F1 (or any cell that is part of the input range of the control).</li> 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.</li></ol>

<div class="references_section">