Skip to content

Excel VBA – How to Apply Data Validation Drop Down List Using VBA Macro

    Use the Add method of the DataValidation property of the Range object.

    expression.Validation.Add(Type, AlertStyle, Operator,Formula1, Formula2)

    Use the Type parameter to specify type of validation

    NameValue
    xlValidateCustom7
    xlValidateDate4
    xlValidateDecimal2
    xlValidateInputOnly0
    xlValidateList3
    xlValidateTextLength6
    xlValidateTime5
    xlValidateWholeNumber1

    Use the AlertStyle parameter to specify the style of the alert warning dialog.

    NameValue
    xlValidAlertInformation3
    xlValidAlertStop1
    xlValidAlertWarning2

    Use the Operator parameter together with the Formula parameters to specify a rule.

    NameValue
    xlBetween1
    xlEqual3
    xlGreater5
    xlGreaterEqual7
    xlLess6
    xlLessEqual8
    xlNotBetween2
    xlNotEqual4

    Specify the error title and error message of the alert dialog in the DataValidation property itself (see code below).

    This code below relates to the table shown above. Column A’s validation allow dates, but not in the past. Columns C and D have been given drop down lists. Each list has a named source – Properties and Issue_Type.

    With Range("A2:A10").Validation
       .Add Type:=xlValidateDate, Operator:=xlGreaterEqual, Formula1:=Date, _ 
        AlertStyle:=xlValidAlertStop
       .ErrorTitle = "ReportingDate"
       .ErrorMessage = "You cannot enter a reporting date as a date in the past"
    End With
    With Range("C2:C10").Validation
       .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:="=Properties", _
        AlertStyle:=xlValidAlertStop
       .ErrorTitle = "Property"
       .ErrorMessage = "Please select a property from the list"
    End With
    With Range("D2:D10").Validation
       .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:="=Issue_Type", _
        AlertStyle:=xlValidAlertStop
       .ErrorTitle = "IssueType"
       .ErrorMessage = "Please select an Issue Type from the list"
    End With