How to Make a Drop Down List From a Macro

Drop down lists in Excel are super helpful when you want to limit a user’s input to a set of predetermined choices. Normally, drop down lists can be made by clicking on a cell, going to the data tab, then by using the Data Validation function. Change Allow to List. 

But what if you’re importing data from another Excel file and want to automatically create a drop down list? It’s possible to do using VBA. To create a drop down list from a macro in cell A1, try this code in the VBA editor:

 

Sub ListCreator()

Range("A1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:="OK,Maybe,No"

End Sub

 

Here, all the entries in the list will be created from the macro. What if one of the entries needs to change? Only someone with VBA experience will probably be able to figure out how to change it. So I like to create the list somewhere within the spreadsheet where anyone can see it and it’s obvious what it’s used for, then use the Name Manager to create a Named Range to easily refer to the list. This way, if you decide to add entries later the lists will automatically be updated as long as they used the Named Range.

 

In this example, I select my list within the Excel sheet (column G), and give it the name “Status”.

To used a Named Range when you create a drop down list via VBA, simply refer to the Named Range:

 

Range("A2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Status"

 

To set a default value, simply set the value of the cell after changing the data validation:

 

Range("A2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Status"

 

Range("A2").Value = “OK”

 

It might be easier to understand by watching this short video:

 

That’s all there is to it! Now you know how to add a drop down list from a macro in Excel. How often do you use drop down lists? I’d love to know, so let me know in the comments below.