How to Populate a Cell After a Drop-Down Selection

You can create a user interface in Excel.

Populating a Microsoft Excel cell after a drop-down selection using a "ComboBox" control can make your Excel application more dynamic. You can create user interfaces with controls such as combo boxes in Excel. Users can select items from the controls and you can program the item selected to populate a cell on the spreadsheet. The "Range" collection will return a range object that represents a single cell or a range of cells.

Instructions

1. Launch Microsoft Office Excel, click the "Developer" tab and click "Visual Basic." Click the "Insert" menu in the VBA Editor window and click "UserForm."

2. Click "ComboBox" on the "Toolbox" pane and click the form to add a new "ComboBox." Right-click the form and click "View Code" to open the VBA module. Copy and paste the following code to add three items when the form initializes:

   Private Sub UserForm_Initialize()

   Me.ComboBox1.AddItem ("item 1")

   Me.ComboBox1.AddItem ("item 2")

   Me.ComboBox1.AddItem ("item 3")

   End Sub

3. Double-click the "ComboBox" control on the form to create a "Change" event. Add the following code to populate cell A1 with the item selected in the "ComboBox" control:

   Range("A1").Select

   Range("A1").Value = Me.ComboBox1.Text

   MsgBox "Cell A1 has been populated!"

4. Press "F5" to run the program and select an item in the "ComboBox" control.

References

• Microsoft: MSDN -- AddItem Method
• Microsoft: MSDN -- Range Collection