How to Use Combo Box in Excel

In this Excel tutorial lesson, you will learn how to insert a combo box into your spreadsheet.

A combo box is a type of drop-down list from which you can choose one option. As for the button, you can assign macro to your Combo Box, which starts after the change of the selected value from the list. But more often, the Combo Box is filled with data from cells. The data source for the list range is arranged in a single column of cells. The cell link returns the ordinal number of the selected item in the list.

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)

Excel Insert Combo Box

Next, put the Combo Box in the Sheet.

Excel Combo Box Inserting

A combo box is inserted.

Excel Combo Box Inserted

Next, right-click and go to Format Control.

Excel Combo Box Format Control

A 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

Combo box Format Object

Your combo box is ready.

See also  How to Create an Excel add in

Excel Combo Box

You can use this Combo Box to create dynamic chart or formula in Excel.

Add a Combo Box to the Worksheet (ActiveX controls)

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

Insert Combo Box

Drag a combo box onto your Excel worksheet.

Insert Combo Box in Sheet

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

Combo box VBA View Code

You will now see this window.

New VBA code

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 on sheet1 then write “With Sheet1.ComboBox1”. If it’s on sheet3 then write “With Sheet3.ComboBox1”. You can add as many items as you like using the .AddItem “London”

This is the result.

Ready drop-down list

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.

Combobox Properties

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

Combo Box connected with Cell

Key Takeaways

  • A combo box is a type of drop-down list from which you can choose one option.
  • You can add a combo box to an Excel worksheet using the Form Controls or ActiveX Controls.
  • To add items to a combo box, you can use the VBA code or the Format Control dialog box.
  • You can link a combo box to a cell to track the selected item.

FAQ

  • Q: How do I add a combo box to an Excel worksheet using the Form Controls?
  • A: To add a combo box to an Excel worksheet using the Form Controls, first go to the Excel Ribbon. Click Developer > Insert > Combo Box (in the Form Controls section). Next, put the Combo Box in the Sheet. A combo box is inserted.
  • Q: How do I add items to a combo box using the VBA code?
  • A: To add items to a combo box using the VBA code, right-click on the combo box (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 and End Sub.
    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.

  • Q: How do I link a combo box to a cell?
  • A: To link a combo box to a cell, right-click on the combo box (make sure design mode is selected) and select properties. In “linkedCell” write the cell reference. For example, if you want to link the combo box to cell A1, you would write “A1” in the “linkedCell” property.