Combo Box

A combo box is a drop-down list from where a user can select an item or fill in his/her own choice. To create a combo box in Excel VBA execute the following steps.

1. On the Developer tab click Insert.

2. In the ActiveX Controls group click Combo Box.

3. Drag a combo box on your worksheet.

Note: you can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now we will leave ComboBox1 as the name of the combo box.

Create a Workbook Open Event. Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook.

4. Open the Visual Basic Editor.

5. Double click on This Workbook in the Project Explorer.

6. Choose Workbook from the left drop-down list and choose Open from the right drop-down list.

7. To add items to the combo box add the following code lines to the Workbook Open Event:

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

Note: use Sheet2 if your combo box is located on the second worksheet Sheet3 if your combo box is located on the third worksheet etc. If you use these code lines outside the Workbook Open event you might want to add the code lines below before these code lines. The first code line clears the combo box. This way your items won’t be added multiple times if you execute your code more than once. The second code line clears your own choice.

ComboBox1.Clear
ComboBox1.Value = “”

8. To link this combo box to a cell right click on the combo box (make sure design mode is selected) and click on Properties. Fill in D2 for LinkedCell.

Note: also see the ListFillRange property to fill a combo box with a range of cells.

9. Save close and reopen the Excel file.

Result:

Although in some situations it can be useful to directly place a combo box on your worksheet a combo box is particularly useful when placed on a Userform.