Create dropdown lists using the Validation with Excel VBA

VBA Code to Create a Dropdown List in Excel

This code will:

1. Define a range in a worksheet.
2. Populate it with a list of values.
3. Apply data validation to create a dropdown list in a target cell.

Sub CreateDropdownList()
    Dim ws As Worksheet
    Dim rng As Range
    Dim targetCell As Range
    Dim listRange As Range   
    ' Set worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the range where the dropdown options will be stored
    Set rng = ws.Range("A1:A5")   
    ' Populate the range with dropdown options
    rng.Value = WorksheetFunction.Transpose(Array("Option 1", "Option 2", "Option 3", "Option 4", "Option 5"))   
    ' Define the target cell where the dropdown list will be applied
    Set targetCell = ws.Range("C1")   
    ' Set up the validation list
    With targetCell.Validation
        ' Remove existing validation
        .Delete
        ' Add new validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & rng.Address
        ' Optional: Display an input message when the cell is selected
        .InputTitle = "Select an Option"
        .InputMessage = "Choose from the list"
        ' Optional: Customize error message
        .ErrorTitle = "Invalid Selection"
        .ErrorMessage = "Please select a valid option from the dropdown list."
        .ShowInput = True
        .ShowError = True
    End With  
    ' Notify the user
    MsgBox "Dropdown list created in " & targetCell.Address, vbInformation, "Success"
End Sub

Explanation

1. Define Worksheet and Ranges
   • The script works on Sheet1, but you can change the sheet name as needed.
   • rng (A1:A5) holds the dropdown values.
   • targetCell (C1) is where the dropdown will appear.
2. Populate the Dropdown List
   • The rng.Value = WorksheetFunction.Transpose(Array(…)) fills the list dynamically.
3. Apply Data Validation
   • .Delete removes any existing validation in the target cell.
   • .Add Type:=xlValidateList creates a dropdown list.
   • Formula1:= »= » & rng.Address links the list source.
   • .InputTitle and .InputMessage show hints when selecting the cell.
   • .ErrorTitle and .ErrorMessage display custom error messages.
4. Notify the User
   • MsgBox confirms that the dropdown list was successfully created.