Validate Data Entry with Excel VBA

The validation will check if the entered data meets certain conditions like data type, length, or whether the data falls within a specific range. I’ll explain each part of the code in detail.

Scenario:

We want to validate data entry in a worksheet, particularly in column « A » where the user can input:

• A number greater than 0.
• A valid date in column « B » (in format « mm/dd/yyyy »).
• Ensure that the entered text in column « C » is a non-empty string of at least 3 characters.
• A valid email address in column « D » (like « user@example.com« ).

Excel VBA Code:

Sub ValidateDataEntry()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cellA As Range, cellB As Range, cellC As Range, cellD As Range
    Dim valid As Boolean
    ' Set worksheet reference
    Set ws = ThisWorkbook.Sheets("Sheet1")  
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Loop through each row from row 2 to lastRow
    For i = 2 To lastRow
        ' Set references for each column in the current row
        Set cellA = ws.Cells(i, 1) ' Column A: Number validation
        Set cellB = ws.Cells(i, 2) ' Column B: Date validation
        Set cellC = ws.Cells(i, 3) ' Column C: Text validation
        Set cellD = ws.Cells(i, 4) ' Column D: Email validation      
        ' Initialize valid flag as true
        valid = True
        ' Validate number in column A (greater than 0)
        If Not IsNumeric(cellA.Value) Or cellA.Value <= 0 Then
            cellA.Interior.Color = RGB(255, 0, 0) ' Red background for invalid entry
            valid = False
        Else
            cellA.Interior.Color = RGB(255, 255, 255) ' Reset to white background
        End If
        ' Validate date in column B (should be a valid date)
        If Not IsDate(cellB.Value) Then
            cellB.Interior.Color = RGB(255, 0, 0) ' Red background for invalid date
            valid = False
        Else
            cellB.Interior.Color = RGB(255, 255, 255) ' Reset to white background
        End If
        ' Validate non-empty text with minimum 3 characters in column C
        If Len(Trim(cellC.Value)) < 3 Or Trim(cellC.Value) = "" Then
            cellC.Interior.Color = RGB(255, 0, 0) ' Red background for invalid text
            valid = False
        Else
            cellC.Interior.Color = RGB(255, 255, 255) ' Reset to white background
        End If       
        ' Validate email format in column D (simple pattern check)
        If Not IsValidEmail(cellD.Value) Then
            cellD.Interior.Color = RGB(255, 0, 0) ' Red background for invalid email
            valid = False
        Else
            cellD.Interior.Color = RGB(255, 255, 255) ' Reset to white background
        End If       
        ' If the entry is not valid, show a message and stop the loop
        If Not valid Then
            MsgBox "Data entry is invalid in row " & i, vbExclamation
            Exit Sub
        End If
    Next i   
    MsgBox "All data entries are valid!", vbInformation
End Sub

' Function to check if the email format is valid
Function IsValidEmail(ByVal email As String) As Boolean
    Dim emailPattern As String
    Dim regEx As Object   
    ' Basic pattern for an email address (very simple)
    emailPattern = "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"
    ' Create RegExp object
    Set regEx = CreateObject("VBScript.RegExp")   
    regEx.IgnoreCase = True
    regEx.Global = True
    regEx.IgnoreCase = True
    regEx.Pattern = emailPattern   
    ' Return whether the email matches the pattern
    IsValidEmail = regEx.Test(email)
End Function

Explanation:

1. Worksheet and Data Range Setup:
   • Set ws = ThisWorkbook.Sheets(« Sheet1 ») assigns the worksheet (Sheet1) for data entry.
   • lastRow is calculated to determine the last row with data in column A, which ensures the code runs only through rows that contain data.
2. Looping Through Rows:
   • The code loops from row 2 to lastRow (since row 1 is typically a header) and validates the data in each column for each row.
3. Data Validation:
   • Column A (Numeric Value Check):
     • If Not IsNumeric(cellA.Value) Or cellA.Value <= 0 checks whether the value in column A is numeric and greater than 0. If not, it highlights the cell red using cellA.Interior.Color = RGB(255, 0, 0).
   • Column B (Date Check):
     • If Not IsDate(cellB.Value) verifies if the value in column B is a valid date. If not, it highlights the cell red.
   • Column C (Text Length Check):
     • If Len(Trim(cellC.Value)) < 3 Or Trim(cellC.Value) = «  » ensures that the text in column C is at least 3 characters long and non-empty.
   • Column D (Email Validation):
     • A custom function IsValidEmail is used to check whether the entered text in column D matches a basic email pattern using regular expressions.
4. Error Handling:
   • If any of the validation checks fail for a row, the row’s corresponding cell is highlighted in red, and a message box pops up indicating which row has invalid data.
   • Exit Sub is used to stop the validation process when the first invalid entry is encountered.
   • If all entries are valid, a success message is displayed after the loop completes.
5. Email Validation with Regular Expressions:
   • A RegExp object is used to validate email format by matching the entered text against a simple pattern for emails (this can be enhanced as needed).

How to Use:

1. Open the Excel workbook where you want to apply data validation.
2. Press Alt + F11 to open the VBA editor.
3. Insert a new module by clicking Insert > Module.
4. Copy and paste the VBA code into the module.
5. Press F5 or run the ValidateDataEntry macro to validate the data in your worksheet.

Possible Enhancements:

• You could extend the email validation regex to be more thorough.
• Add more specific range or type checks for numbers (e.g., integer, specific range).
• Enhance the UI by using MsgBox to highlight all invalid rows after the check, instead of stopping after the first invalid entry.