How to Create a Data Validation Drop-Down List with VBA in Excel (7 Methods)

Method 1 – Embedding VBA to Create a Data Validation Drop-Down List 

Steps:

• Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Enter the following code into the code window:

Sub CreateDropDownList()
Range("B5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Grapes, Orange, Guava, Mango, Apple"
End Sub

Your code is now ready to run.

This piece of code will create a dropdown list in Cell B5. The dropdown list will have the values “Grapes, Orange, Guava, Mango, Apple.”

• Press F5 or from the menu bar, select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the code execution, look at the image below for the result.

As we can see from the above image, a dropdown list with the values “Grapes, Orange, Guava, Mango, Apple” was created in Cell B5.

Read More: VBA to Select Value from Drop-Down List in Excel

Method 2 – Generating a Drop-Down List by Named Range with VBA 

Steps:

• Select the range where the values of the dropdown list are present (in our case, the range is B5:B9).
• Right-click on the selected range.
• A list of options will appear. Select Define Name…

• A New Name pop-up box will appear. Enter any name that you like (we named our cell Fruits).
• Click OK.

• We have successfully named the range B5:B9 Fruits (shown in the picture below).

Use this defined name in our VBA code. The steps to take are shown below.

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, enter the following code:

Sub GenerateDropDownList()
Range("B12").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"
End Sub

Your code is now ready to run.

This code will create a dropdown list in Cell B12 with the values “Grapes, Orange, Guava, Mango, Apple” defined in the name Fruits.

• Run the macro. The result is shown in the image below.

As a result, the above image shows a dropdown list created in Cell B12 with the values “Grapes, Orange, Guava, Mango, Apple.”

Read More: How to Use Named Range for Data Validation List with VBA in Excel

Method 3 – Producing a Data Validation Drop-Down Box from a Given List with Macro

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Enter the following code into the code window:

Sub ProduceDropDownList()
With Range("B12").Validation
 .Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$10"
 .InCellDropdown = True
End With
End Sub

Your code is now ready to run.

This code will produce a dropdown list in Cell B12 with the values present in the range B5:B9.

• Run the macro and look at the following image to see the output.

As a result, we can see from the above image that a dropdown list with the values “Grapes, Orange, Guava, Mango, Apple” was created in Cell B12, which we stored in Cells B5 to B9 in the worksheet.

Method 4 – Implementing VBA to Create Multiple Drop-Down Lists 

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, enter the following code:

Sub MultipleDropDownList(iTarget As Range, iSource As Range)
    'to delete and add validation in the target range
    With iTarget.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & iSource.Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Sub DropDownRange()
    MultipleDropDownList Sheet7.Range("B5:B10"), Sheet7.Range("A1:A3")
End Sub

Your code is ready to run.

This code will generate a dropdown list in every cell from range B5 to B9.

• Run the macro. Look at the gif below to see the result.

Each cell from range B5 to B9 now holds a dropdown list.

Method 5 – Applying VBA to Create a Drop-Down List with User-Defined Function

Steps:

• Right-click on the sheet where you want to implement the UDF to create a dropdown list.
• Select View Code from the appeared list. As shown below, we right-clicked on the sheet named UDF, where our dataset is stored, and selected View Code from the options.

• Enter the following code into the auto-generated code window:

Public Function DropDownUDF(iSource As Range) As Variant
    'to delete and add validation in the specified range
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & iSource.Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    'this will return the first value
    'this reset the values when formula in sheet are refreshed
    DropDownUDF = VBA.Val(iSource(1))
End Function

• Don’t run this code. Save it.

• Go back to the worksheet of interest.
• Pick any cell where you want to create the dropdown list (in our case, Cell B11).
• Enter the newly created function – DropDownUDF – how you write any other function. First, put an equal sign (=), write the function name, DropDownUDF, and pass the cell references (B5:B9) inside the parentheses.

The formula in Cell B11 will be like this:

• Press Enter.

You will get a dropdown list created by UDF in Cell B11 with the values “Grapes, Orange, Guava, Mango, Apple” that are stored in range B5:B9 and which we passed inside the function.

Method 6 – Extracting Data from a Different Sheet in a Drop-Down List with VBA

Look at the following image. We have a dataset in the sheet named List.

We are going to create a dropdown list in Cell B5 of the sheet named Target (shown in the picture below). The values in that dropdown list will be those from the range B5:B9 of the List sheet.

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Enter the following code into the code window:

Private Sub DropDownFromSheet()
'to store the dropdown list in cell B5
'you can replace "B5" with any other cell
With Range("B5").Validation
.Delete
'to extract data from "List" sheet and "B5:B9" range
'you can replace "=List!B5:B9" with your sheet name and range
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=List!B5:B9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Your code is now ready to run.

• Run the macro and look at the following image to see the output.

A dropdown list with the values “Grapes, Orange, Guava, Mango, Apple” was created in Cell B5 of the Target worksheet. The list is stored in range B5:B9 of the List spreadsheet.

Read More: How to Make a Dynamic Data Validation List Using VBA in Excel

Method 7 – Deleting a Data Validation Drop-Down List with VBA Macro

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, enter the following code:

Sub DeleteDropDownList()
Range("B5").Validation.Delete
End Sub

Your code is now ready to run.

• Run the macro and look at the following image.

There is no dropdown list in Cell B5. We have learned how to delete an existing dropdown list from a spreadsheet with VBA.

Related Articles

• Excel VBA to Create Data Validation List from Array
• Unique Values in a Drop Down List with VBA in Excel
• How to Create Dynamic Drop Down List Using VBA in Excel