Add Excel VBA Drop Down Data Validation List

How to add Data validation drop down list in Excel VBA?

Two different methods to create drop down list are explained here – but all are related to validation list.

Note: Combo-box control that also display list of values in dropdown is not explained in this page.

1. Adding Drop Down to Range of Cell
2. Create User defined function
   • To add dropdown at any location using formula

Let’s see how to get an Excel drop down list using VBA Macro.

1. [Vba] Add Drop Down Validation list to Range

For example, lets assume you would like to add validation list to cell range D1:D100. This below sub procedure can be used for this purpose.

Sub DropDownToRange(rngTarget As Range, rngSource As Range)
    'Delete & Add Validation in Target Range
    With rngTarget.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & rngSource.Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

The above procedure takes target cells range & validation list for drop down source range as two parameters. You can call the above function as given in this test function.

Sub TestDropDown()
    DropDownToRange Sheets(1).Range("D1:D100"), Sheets(1).Range("A1:A3")
End Sub

Once you run this above test code, it will add drop down list in cells from D1 to D100.

2.Formula to Add Dynamic Drop Down Validation List [VBA-UDF]

This step is about creating a formula & then adding drop down list in any cell using the formula.

Note: Don’t execute this code with F5. Go to any sheet. Follow the steps explained below the code.

Public Function DropDownInLine(rngSource As Range) As Variant
    'Delete & Add Validation in Selected Cell
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & rngSource.Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    'Return first value
    'This is just an illustration - It is better to omit this line.
    'If formula in sheet are refreshed, this reset the values
    DropDownInLine = VBA.Val(rngSource(1))
End Function

The above code should be added to a module in VBA project. Just follow these steps to create a dynamic list in your Excel.

1. Press Alt + F11 in Excel to view VB Editor.
2. Click on Insert Menu & choose Module.
3. This will insert a new module in current project.
4. Copy paste the above code to this module.

After adding the code, follow these steps.

Sample Data Source Setup:

Enter these values in Column A. Value1 in A1, Value2 in A2, Value3 in A3.

Then in Cell B1 enter “=DropDownInLine(A1:A3)” and press Enter.

Now, you can see B1 has a drop down list added with default value as first entry in list. Yes. We have added a new formula to Excel that can add a Validation list on the Go.

Add Values to Drop down list using a variable

In all the code snippets above, drop down list source values are taken from range. What if you need to add list from a variable string?

Just change the AddrRange.Range with a string variable as in below example.

 ListValue = "Value1, Value2, Value3, Value4"
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
      xlBetween, Formula1:="=" & ListValues

Isn’t this so easy than to mention the range address? It is easy to create this list dynamically from within the Excel VBA code.

What is a Drop down list box menu in Excel?

Data validation drop down list in Excel, is an array of predefined values that users can pick.

In a cell, if you want the user to only pick values like “Yes or No” or any such list of values, then we go for this Drop down Data Validation lists in Excel. In this cell, users will be allowed to type only the values that are already mentioned in the validation list or drop down list.

Now, you can see a drop down button in the Excel worksheet cell that we created a validation list. Excel will give a warning message if we try to enter a value that is not present in the Drop down list.

Note: The above steps explain adding drop down list or Data validation to a single cell. But, this can also be applied to range of cells. Instead of choosing one cell, select range of cell and then follow from step2.