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.
- This article explains how to create a drop-down list in Excel that pulls data from a different worksheet. This allows you to enter data into a specific cell of a worksheet from a preset list of entries. Instructions cover Excel 2019, 2016, 2013, and 2010 and Excel for Mac 2019, 2016, and 2011.
- Source = the range with the values you want for the auto-complete / drop-down. You should now have a drop-down but with a weak auto-complete feature. With a cell containing your newly added data validation, insert an ActiveX combo box (NOT a form control combo box). This is done from the Developer ribbon.
6. Choose Workbook from the left drop-down list and choose Open from the right drop-down list.
When you work with data in Excel, often you need to add more and more of new data. However, it takes some time to update formulas, charts, tables, when you add new data. Excel proposes functions that can help to customize your data and avoid routine updating. This tip is about creating an automatically extended (also known as dynamically updated or just dynamic) drop-down lists.
Excel Online
7. To add items to the combo box, add the following code lines to the Workbook Open Event:
.AddItem 'Paris'
.AddItem 'New York'
.AddItem 'London'
EndWith
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.
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.
Excel
Note: also see the ListFillRange property to fill a combo box with a range of cells.
Developer For Excel 2016 Autocomplete Drop Down List
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.