Develop Customized Data Validation Checks with Excel VBA

The code includes validation checks for various types of data and provides feedback to the user when invalid data is entered.

Objective:

We are creating a customized data validation solution using VBA in Excel. This will include:

• Validating different data types (e.g., numeric, date, text length, custom formulas).
• Displaying messages when invalid data is entered.
• Preventing invalid entries or correcting them automatically.

Setup and Preparation:

Before we begin with the code, ensure that macros are enabled in Excel and that the VBA editor is open.

To open the VBA editor:

1. Press Alt + F11 to open the VBA editor.
2. Insert a new module via Insert -> Module in the VBA editor.

VBA Code for Customized Data Validation:

This code will perform the following tasks:

• Validate if a cell contains a numeric value.
• Validate if a cell contains a date in a certain range.
• Check for a specific text length.
• Use a custom validation formula.

Here is the detailed code:

Sub CustomizedDataValidationChecks()
    Dim ws As Worksheet
    Dim cell As Range
    Dim inputValue As Variant
    Dim isValid As Boolean
    Dim validationType As String   
    ' Set the target worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Modify as needed
    ' Loop through the range of cells to validate (you can adjust the range)
    For Each cell In ws.Range("A1:A10") ' Modify this range as needed
        inputValue = cell.Value
        isValid = True ' Assume the value is valid unless proven otherwise       
        ' Skip empty cells
        If IsEmpty(inputValue) Then GoTo ContinueLoop       
        ' Determine the type of validation based on the column or another condition
        validationType = DetermineValidationType(cell)       
        Select Case validationType
            Case "Numeric"
                ' Check if the value is numeric
                If Not IsNumeric(inputValue) Then
                    MsgBox "Invalid entry in cell " & cell.Address & ". Please enter a numeric value.", vbExclamation
                    cell.ClearContents ' Clear the invalid entry
                    isValid = False
                End If               
            Case "Date"
                ' Check if the value is a valid date and falls within a certain range
                If Not IsDate(inputValue) Then
                    MsgBox "Invalid date in cell " & cell.Address & ". Please enter a valid date.", vbExclamation
                    cell.ClearContents
                    isValid = False
                Else
                    ' Check if the date is within a specific range (e.g., between 01/01/2020 and 12/31/2025)
                    If inputValue < DateSerial(2020, 1, 1) Or inputValue > DateSerial(2025, 12, 31) Then
                        MsgBox "Date in cell " & cell.Address & " is out of the allowed range. Please enter a date between 01/01/2020 and 12/31/2025.", vbExclamation
                        cell.ClearContents
                        isValid = False
                    End If
                End If               
            Case "TextLength"
                ' Check if the length of the text is within a specific range
                If Len(inputValue) < 5 Or Len(inputValue) > 20 Then
                    MsgBox "Text in cell " & cell.Address & " must be between 5 and 20 characters long.", vbExclamation
                    cell.ClearContents
                    isValid = False
                End If               
            Case "CustomFormula"
                ' Use a custom formula for validation (e.g., check if the value starts with a specific letter)
                If Not inputValue Like "A*" Then
                    MsgBox "Value in cell " & cell.Address & " must start with the letter 'A'.", vbExclamation
                    cell.ClearContents
                    isValid = False
                End If               
            Case Else
                ' Default validation (if needed)
                MsgBox "No validation rule defined for cell " & cell.Address, vbInformation
        End Select       
        ' Continue to the next cell if validation fails
ContinueLoop:
    Next cell   
    MsgBox "Data validation check completed!", vbInformation
End Sub

' Function to determine the validation type based on the column or other criteria
Function DetermineValidationType(cell As Range) As String
    If cell.Column = 1 Then
        ' Column A will have numeric validation
        DetermineValidationType = "Numeric"
    ElseIf cell.Column = 2 Then
        ' Column B will have date validation
        DetermineValidationType = "Date"
    ElseIf cell.Column = 3 Then
        ' Column C will have text length validation
        DetermineValidationType = "TextLength"
    ElseIf cell.Column = 4 Then
        ' Column D will have custom formula validation
        DetermineValidationType = "CustomFormula"
    Else
        ' Default case
        DetermineValidationType = "Default"
    End If
End Function

Explanation of the Code:

1. Worksheet and Range Setup:
   • The code starts by defining the worksheet ws where the validation will occur.
   • The range Range(« A1:A10 ») specifies that the validation checks will apply to the cells within this range. You can change the range based on your needs.
2. Loop Through Each Cell:
   • The code loops through each cell in the defined range and retrieves the value entered in the cell (inputValue).
3. Validation Based on Column:
   • The DetermineValidationType function is used to assign a specific validation type to each column. For example:
     • Column 1 (A) will have numeric validation.
     • Column 2 (B) will validate dates.
     • Column 3 (C) will check for text length.
     • Column 4 (D) will use a custom formula for validation.
   • This allows for flexibility in the type of validation for different columns.
4. Data Validation Checks:
   • Numeric Validation: Ensures that the entered value is a number. If it’s not, it shows an error message and clears the cell.
   • Date Validation: Checks whether the value is a date and falls within a specific range (01/01/2020 to 12/31/2025).
   • Text Length Validation: Ensures that the length of the text entered is between 5 and 20 characters.
   • Custom Formula Validation: In this example, the custom rule checks if the value starts with the letter « A ». You can adjust the formula as needed.
5. Error Message:
   • If an entry is invalid, a MsgBox appears to alert the user about the specific error.
   • The invalid data is cleared from the cell (cell.ClearContents), so users must correct it.

How to Run the Code:

1. Open the VBA Editor (Alt + F11).
2. Insert a Module (Click Insert -> Module).
3. Paste the Code into the module.
4. Run the Code by pressing F5 or through the Run button in the editor.

Customization Tips:

• Modify the Range(« A1:A10 ») to validate any other range of cells as required.
• You can adjust the validation rules inside the Select Case block to fit your needs, such as adding more validation types.
• For the custom formula validation, replace Like « A* » with your desired condition (e.g., check if the value is a specific length, matches a regex, etc.).

This approach provides a flexible and robust way to handle custom data validation checks in Excel using VBA.