Data Validation in Excel VBA

1. Basics of Data Validation in Excel VBA

Data Validation rules restrict the type of data that can be entered in a cell. These rules include:

• Whole Number
• Decimal
• List
• Date
• Time
• Text Length
• Custom Formula

In VBA, Data Validation is controlled using the Validation object of the Range class.

2. Syntax for Adding Data Validation in VBA

To apply data validation, we use:

Range(« A1 »).Validation.Add Type, AlertStyle, Operator, Formula1, Formula2

Where:

• Type: Specifies the type of validation (e.g., xlValidateWholeNumber, xlValidateList).
• AlertStyle: Defines the alert style (xlValidAlertStop, xlValidAlertWarning, xlValidAlertInformation).
• Operator: Specifies an operator for comparison (xlBetween, xlGreater, xlLess, etc.).
• Formula1: First parameter of validation (e.g., minimum value).
• Formula2: Second parameter (used for range-based validation).

3. VBA Code Examples for Different Data Validation Types

3.1 Whole Number Validation (Between 1 and 100)

Sub ValidateWholeNumber()
    With Range("B2").Validation
        .Delete
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=1, Formula2:=100
        .InputTitle = "Enter a Number"
        .ErrorTitle = "Invalid Entry"
        .InputMessage = "Please enter a whole number between 1 and 100."
        .ErrorMessage = "Only numbers between 1 and 100 are allowed."
        .ShowInput = True
        .ShowError = True
    End With
End Sub

• .Delete clears any existing validation before applying new rules.
• .InputTitle and .InputMessage provide guidance when the user selects the cell.
• .ErrorTitle and .ErrorMessage define what appears if validation fails.

3.2 Decimal Validation (Greater than 10.5)

• Sub ValidateDecimal()
      With Range("C2").Validation
          .Delete
          .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, _
               Operator:=xlGreater, Formula1:=10.5
          .InputTitle = "Decimal Entry"
          .ErrorTitle = "Invalid Decimal"
          .InputMessage = "Enter a decimal greater than 10.5."
          .ErrorMessage = "Value must be greater than 10.5."
      End With
  End Sub

  Ensures that only decimal numbers greater than 10.5 are allowed.

3.3 List Validation (Dropdown Menu)

Sub ValidateList()
    With Range("D2").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:="Apple,Banana,Cherry"
        .InputTitle = "Select a Fruit"
        .ErrorTitle = "Invalid Choice"
        .InputMessage = "Choose a fruit from the dropdown list."
        .ErrorMessage = "Only Apple, Banana, or Cherry are allowed."
    End With
End Sub

• Creates a dropdown list with predefined values.

3.4 Date Validation (Between Two Dates)

Sub ValidateDate()
    With Range("E2").Validation
        .Delete
        .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:="01/01/2023", Formula2:="12/31/2023"
        .InputTitle = "Enter a Date"
        .ErrorTitle = "Invalid Date"
        .InputMessage = "Enter a date between 01/01/2023 and 12/31/2023."
        .ErrorMessage = "Date must be between the specified range."
    End With
End Sub

• Ensures that the entered date is within the specified range.

3.5 Custom Formula Validation (Only Even Numbers)

Sub ValidateCustomFormula()
    With Range("F2").Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _
             Formula1:="=MOD(F2,2)=0"
        .InputTitle = "Even Numbers Only"
        .ErrorTitle = "Invalid Entry"
        .InputMessage = "Please enter an even number."
        .ErrorMessage = "Only even numbers are allowed."
    End With
End Sub

• Uses a custom formula to allow only even numbers.

4. Clearing Data Validation in VBA

To remove validation from a specific range:

Sub ClearValidation()
    Range("A1:F10").Validation.Delete
End Sub

To clear validation from the entire worksheet:

Sub ClearAllValidation()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Cells.Validation.Delete
End Sub

5. Checking If a Cell Has Data Validation

To check if a cell has validation:

Sub CheckValidation()
    If Range("A1").Validation.Type <> xlValidAlertStop Then
        MsgBox "Data Validation is applied.", vbInformation, "Validation Check"
    Else
        MsgBox "No Data Validation found.", vbExclamation, "Validation Check"
    End If
End Sub

6. Applying Data Validation to a Dynamic Range

This example applies a dropdown list validation to a dynamic range:

• Sub DynamicValidation()
      Dim lastRow As Long
      lastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Find last used row in column A
      With Range("B2:B" & lastRow).Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
               Formula1:="Apple,Banana,Cherry"
          .InputMessage = "Select a fruit."
          .ErrorMessage = "Invalid selection!"
      End With
  End Sub

  Automatically detects the last row and applies validation dynamically.

           7. Using Named Ranges in Data Validation

To use a named range in list validation:

Sub ValidateNamedRange()
    With Range("G2").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Formula1:="=FruitList" ' FruitList is a named range
        .InputTitle = "Select a Fruit"
        .InputMessage = "Choose a fruit from the list."
    End With
End Sub

Make sure « FruitList » is a named range containing values.

      Conclusion

VBA makes it easy to automate Data Validation in Excel, ensuring accurate data entry and enhancing user experience. You can:

✔ Apply different types of validation
✔ Use dynamic ranges for scalability
✔ Customize error and input messages
✔ Use formulas for advanced validation