Excel Combo Box: Dynamic Data Selection

Add a Combo Box to the Worksheet (form controls)

To add a Combo Box on an Excel worksheet first go to the Excel Ribbon. Click Developer > Insert > Combo Box (in the Form Controls section)

Next put the Combo Box in the Sheet.

Combo Box is inserted.

Next right-click and go to Format Control.

Format Object dialog box appears. Go to Control tab.

• • Input range – range of cells which contain values which will apear in your Combo Box

• • Cell link – put here adress of the cell where will apear the number of value from the list. It will help you creating functions of charts based on Combo Box

• • Drop down lines – number of lines in Combo Box – it should be the number of values in list or less

Your Combo Box is ready.

You can use this Combo Box to create dynamic chart or formulas with Excel.

Add a Combo Box to the Worksheet (ActiveX controls)

Click on the combo box icon in the “ActiveX Controls” group.

Drag a combo box on your Excel worksheet.

To add items to your combo box right click on it (make sure design mode is selected) and click on “View Code”.

You will now see this window

Write the following piece of code between

Private Sub
With Sheet1.ComboBox1
.AddItem “Paris”
.AddItem “New York”
.AddItem “London”
End With
End Sub.

After writing the above code you can close the window from the upper right.

Note: If your combo box is located in sheet1 then write “With Sheet1.ComboBox1”.If it’s in sheet3 then write “With Sheet3.ComboBox1”. You can add as many items as you like using the .AddItem “London”

This is the result

Now suppose you would like to link your combo box to a cell E3 for example. Right click on the combo box (make sure design mode is selected) and select properties. In “linkedCell” write E3.

That’s it! You have now successfully created a combo box drop down menu.