VBA Code to Create a Data Validation Drop-down List in Excel

VBA Code:

Sub CreateDropDownList()
    Dim rng As Range
    Dim validationRange As Range
    Dim validationFormula As String
    
    ' Set the range where the drop-down list will be created
    Set rng = Range("A1:A10") ' Change the range as desired
    
    ' Set the range of values for the drop-down list
    Set validationRange = Worksheets("Data Sheet").Range("B1:B5") ' Change the sheet name and range as desired
    
    ' Set the validation formula for the drop-down list
    validationFormula = "=" & validationRange.Address
    
    ' Apply the data validation to the range
    With rng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=validationFormula
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Check All VBA Codes

Join Our Telegram Group techguruplus telegram group Join Our WhatsApp Group techguruplus whatsapp group

Leave a Comment