Excel VBA: How To Create Drop-Down List In Different Ways

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

Posted in Blog and tagged .

Leave a Reply

Your email address will not be published. Required fields are marked *