Automate data validation rules in Excel using VBA

To automate data validation rules in Excel using VBA, you can create macros that apply specific validation rules to certain cells or ranges. Below is a detailed example that explains how to automate data validation with VBA, including simple rules like number validation, date validation, and dropdown lists.

VBA Code Objectives:

1. Apply data validation to allow only numbers in a cell.
2. Apply data validation to allow only dates in a range of cells.
3. Create a dropdown list with predefined options.
4. Add custom error messages when validation rules are not met.

Step-by-step:

1. Validation for Allowing Only Numbers

This example applies data validation to cell A1 to allow only numbers between 1 and 100.

Sub ValidationNumbers()
    ' Select cell A1
    With Range("A1").Validation
        ' Clear any existing validation
        .Delete
        ' Apply whole number validation
        .Add Type:=xlValidateWholeNumber, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="1", Formula2:="100"
        ' Custom error message
        .ErrorMessage = "Please enter a number between 1 and 100."
        .ShowError = True
    End With
End Sub

Explanation:

• Range(« A1 »).Validation applies validation to cell A1.
• Type:=xlValidateWholeNumber specifies that only whole numbers are allowed.
• Formula1:= »1″, Formula2:= »100″ sets the range for valid numbers between 1 and 100.
• .ErrorMessage sets a custom error message.
• .ShowError = True shows the error message if the entered value does not meet the validation.

2. Validation for Allowing Only Dates

This example applies data validation to a range of cells (e.g., B1:B10) to allow only dates within a specific range.

Sub ValidationDates()
    ' Apply validation to range B1:B10
    With Range("B1:B10").Validation
        ' Clear any existing validation
        .Delete
        ' Apply date validation
        .Add Type:=xlValidateDate, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="01/01/2020", Formula2:="31/12/2025"
        ' Custom error message
        .ErrorMessage = "Please enter a date between 01/01/2020 and 31/12/2025."
        .ShowError = True
    End With
End Sub

Explanation:

• xlValidateDate specifies that only dates are allowed.
• Formula1:= »01/01/2020″, Formula2:= »31/12/2025″ sets the valid date range.
• If the user enters a non-date value or a date outside the specified range, the custom error message will appear.

3. Creating a Dropdown List

This example creates a dropdown list in cell C1 with predefined options: « Option 1 », « Option 2 », « Option 3 ».

Sub DropdownList()
    ' Apply dropdown list validation to cell C1
    With Range("C1").Validation
        ' Clear any existing validation
        .Delete
        ' Create a dropdown list with fixed values
        .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="Option 1,Option 2,Option 3"
        ' Custom error message
        .ErrorMessage = "Please select an option: Option 1, Option 2, Option 3."
        .ShowError = True
    End With
End Sub

Explanation:

• xlValidateList specifies that a dropdown list is to be applied.
• Formula1:= »Option 1,Option 2,Option 3″ defines the options in the list.
• If the user enters anything other than the available options, the error message will be shown.

4. Custom Validation with a Formula (e.g., Text Starting with a Specific Letter)

This example validates that the text entered in cell D1 starts with the letter « A ».

Sub TextValidation()
    ' Apply validation to cell D1
    With Range("D1").Validation
        ' Clear any existing validation
        .Delete
        ' Apply custom validation with a formula
        .Add Type:=xlValidateCustom, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="=Left(D1,1)=""A"""
        ' Custom error message
        .ErrorMessage = "Text must start with the letter 'A'."
        .ShowError = True
    End With
End Sub

Explanation:

• xlValidateCustom allows us to use a formula for custom validation.
• Formula1:= »=Left(D1,1)= » »A » » » checks if the first character in D1 is « A » using the LEFT function.
• If the condition is not met, the custom error message will be displayed.

Complete VBA Code for All Validations

Here’s a complete VBA code that applies all the above validations to a worksheet:

Sub ApplyValidations()
    ' Apply number validation in cell A1
    ValidationNumbers   
    ' Apply date validation in range B1:B10
    ValidationDates   
    ' Apply dropdown list in cell C1
    DropdownList 
    ' Apply text validation in cell D1
    TextValidation
End Sub

How to Run This Code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the left pane, right-click on « VBAProject (YourWorkbookName) », then select Insert > Module.
3. Paste the code into the new module.
4. Close the VBA editor.
5. You can now run the macro by pressing Alt + F8, then selecting ApplyValidations.

Conclusion

This code demonstrates how to automate various data validation rules in Excel using VBA. You can customize these validations according to your specific needs, such as setting different ranges, validation types, or custom criteria.