Implement Advanced Data Validation Techniques with Excel VBA

Objective:

We will create a VBA code that implements complex data validation techniques such as:

• Custom Validation Lists that are dynamic and depend on other cell values.
• Date Range Validation ensuring data falls within a specific date range.
• Text Length Validation to restrict the number of characters entered in a cell.
• Formula-based Validation that validates based on a custom formula.

Step-by-Step VBA Code Example

Sub ImplementAdvancedDataValidation()
    Dim ws As Worksheet
    Dim rng As Range
    ' Set the target worksheet and the range where validation will be applied
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Example 1: Custom Dynamic List Validation
    ' The validation will depend on the value of cell A1
    ' If A1 is "Fruits", the list should contain "Apple", "Banana", "Orange"
    ' If A1 is "Vegetables", the list should contain "Carrot", "Potato", "Tomato"
    Set rng = ws.Range("B2:B10") ' Range where validation will be applied
    ' Clear existing validations
    rng.Validation.Delete
    ' Add a dynamic validation list
    If ws.Range("A1").Value = "Fruits" Then
        rng.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                           Operator:=xlBetween, Formula1:="Apple,Banana,Orange"
    ElseIf ws.Range("A1").Value = "Vegetables" Then
        rng.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                           Operator:=xlBetween, Formula1:="Carrot,Potato,Tomato"
    End If
    rng.Validation.IgnoreBlank = True
    rng.Validation.InCellDropdown = True
    ' Example 2: Date Range Validation
    ' Ensures the entered date is between 01-Jan-2020 and 31-Dec-2025
    Set rng = ws.Range("C2:C10")   
    ' Clear existing validations
    rng.Validation.Delete   
    ' Add date range validation
    rng.Validation.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, _
                       Operator:=xlBetween, Formula1:="01/01/2020", Formula2:="31/12/2025"
    rng.Validation.IgnoreBlank = True
    rng.Validation.InCellDropdown = False   
    ' Example 3: Text Length Validation
    ' Restrict text length to be between 5 and 15 characters   
    Set rng = ws.Range("D2:D10")   
    ' Clear existing validations
    rng.Validation.Delete   
    ' Add text length validation
    rng.Validation.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
                       Operator:=xlBetween, Formula1:=5, Formula2:=15
    rng.Validation.IgnoreBlank = True
    rng.Validation.InCellDropdown = False   
    ' Example 4: Formula-based Validation
    ' Ensure that the value in E2:E10 is greater than the value in D2:D10   
    Set rng = ws.Range("E2:E10")   
    ' Clear existing validations
    rng.Validation.Delete   
    ' Add formula-based validation
    rng.Validation.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _
                       Operator:=xlBetween, Formula1:="=E2>D2"
    rng.Validation.IgnoreBlank = True
    rng.Validation.InCellDropdown = False   
    ' Final Message
    MsgBox "Advanced Data Validation has been applied successfully!", vbInformation
End Sub

Detailed Explanation of Each Step

1. Dynamic List Validation Based on Another Cell’s Value (Example 1)

‘ Create a dynamic validation list based on the value of cell A1

If ws.Range("A1").Value = "Fruits" Then
    rng.Validation.Add Type:=xlValidateList, Formula1:="Apple,Banana,Orange"
ElseIf ws.Range("A1").Value = "Vegetables" Then
    rng.Validation.Add Type:=xlValidateList, Formula1:="Carrot,Potato,Tomato"
End If

• Goal: This technique allows you to create a dependent dropdown list. The list options change depending on the value entered in a parent cell (e.g., A1).
• How it works:
  • The Validation.Add method applies data validation to a specified range.
  • If cell A1 contains « Fruits, » the dropdown in B2:B10 will show fruit options. If A1 contains « Vegetables, » the dropdown will show vegetable options.

2. Date Range Validation (Example 2)

' Validate that entered date is between 01-Jan-2020 and 31-Dec-2025
rng.Validation.Add Type:=xlValidateDate, Formula1:="01/01/2020", Formula2:="31/12/2025"

• Goal: This ensures that the data entered is a valid date within a specific date range.
• How it works:
  • The xlValidateDate validation type is used.
  • Formula1 and Formula2 specify the start and end dates of the valid range.
  • If the user enters a date outside this range, Excel will trigger an error message.

3. Text Length Validation (Example 3)

' Validate that the entered text length is between 5 and 15 characters
rng.Validation.Add Type:=xlValidateTextLength, Formula1:=5, Formula2:=15

• Goal: This limits the length of text input in cells to a specific range, preventing excessively short or long entries.
• How it works:
  • The xlValidateTextLength type is used to restrict text input to a range defined by Formula1 (minimum characters) and Formula2 (maximum characters).
  • Users can only enter text that is between 5 and 15 characters in length.

4. Formula-based Validation (Example 4)

' Ensure the value in E2:E10 is greater than the value in D2:D10
rng.Validation.Add Type:=xlValidateCustom, Formula1:="=E2>D2"

• Goal: This validation uses a custom formula to compare values between two columns, ensuring one is greater than the other.
• How it works:
  • The xlValidateCustom validation type allows the use of an Excel formula for validation.
  • The formula « =E2>D2 » checks that the value in column E is greater than the value in column D. If the condition is not met, the user will see an error message.

Additional Features:

• Error Messages: You can customize the error message using ErrorTitle and ErrorMessage properties in the Validation object.
• Data Entry Handling: By setting the InCellDropdown property to True, you ensure the user can see a dropdown for list-based validations.
• Clearing Validations: The Validation.Delete method is used to clear any existing validations before applying new ones.

Conclusion

By using the techniques above, you can create robust data validation rules in Excel through VBA. This allows for dynamic, formula-based, and even context-sensitive validation rules, ensuring the data entered into your Excel worksheets adheres to your specific requirements.