Welcome to Excel Jet Consult blog post. In this tutorial, we will learn different ways of creating a drop-down list in Excel VBA. Let’s get started
The first thing we need to do is to have the Developer tab available: To activate the Developer tab:
- Execute ALT + FT
- In the Excel Options dialogue box, click on Customize Ribbon
- Check Developer tab box
- Click OK
First Method
In the caption below, we want to create drop-down list for the Customer Name
The first thing we will do is apply named range to Customer Name in Column A: To do that:
- Select A1:A7 and execute CTRL + SHIFT + F3
- In the Create Names from Selection dialogue box, the automatically checked Top row is fine. Therefore, click OK
Now, we have a named range Customer_Name
- In the Insert drop-down of the Control group of the Developer tab, locate and click on Combo Box (ActiveX Control) and make a combo box on the spreadsheet
- Right-click on the Combo Box and select Properties
- In the ListFillRange, type in the Customer_Name (which is the named range created earlier).
- Click Enter.
Caveat, ensure the named range is correctly typed inside the ListFillRange box. See the caption above
- Close the Properties dialogue box
- In the Control group of the Developer tab, click on the Design Mode to turn off design mode
When we click on the Combo Box drop-down, we can see the List of the Customer Name; and make selection
Second Method
- In the Insert drop-down of the Control group of the Developer tab, locate and click on Combo Box (ActiveX Control) and make a combo box on the spreadsheet
- Double-click on the Combo Box
- In the VBA Editor code window, from the Change drop-down on the top right of the screen, select DropButtonClick
- Execute the lines of codes as seen in the caption below
- Close the VBA Editor
- In the Control group of the Developer tab, click on the Design Mode to turn off design mode
When we click on the Combo Box drop-down, we can see the List of the Customer Name
