Validate Data Entry with UserForms, Excel VBA

Purpose:

The goal of this project is to create a UserForm in Excel VBA that allows users to enter data. The form will validate the data to ensure it meets specific requirements (e.g., no empty fields, numeric values where appropriate, etc.). If the data is invalid, the form will display an error message and prevent the user from submitting it.

Steps:

1. Create a UserForm:
   • Open Excel.
   • Press Alt + F11 to open the VBA editor.
   • In the VBA editor, go to Insert > UserForm to create a new form.
   • Add controls (TextBoxes, Labels, CommandButtons) for data entry.
2. Add Controls: For this example, we’ll use the following controls on the UserForm:
   • Two TextBox controls for user input (e.g., Name, Age).
   • A CommandButton to submit the form.
   • A Label control to display error messages.
3. Add VBA code to handle validation: The code will validate whether the input fields are filled, check if a number is entered when appropriate, and display error messages if validation fails.

VBA Code:

' UserForm Code
' This is the event handler for the Submit button
Private Sub CommandButtonSubmit_Click()
    ' Clear previous error messages
    LabelError.Caption = ""
    ' Validate Name field
    If TextBoxName.Value = "" Then
        LabelError.Caption = "Name is required."
        TextBoxName.SetFocus
        Exit Sub
    End If   
    ' Validate Age field (numeric check)
    If TextBoxAge.Value = "" Then
        LabelError.Caption = "Age is required."
        TextBoxAge.SetFocus
        Exit Sub
    ElseIf Not IsNumeric(TextBoxAge.Value) Then
        LabelError.Caption = "Please enter a valid number for Age."
        TextBoxAge.SetFocus
        Exit Sub
    End If   
    ' If validation passes, proceed with the next steps (e.g., store data, close the form)
    MsgBox "Data entry is valid. The form will now close.", vbInformation
    ' Example: Storing the data in a worksheet (if necessary)
    Sheets("DataSheet").Range("A1").Value = TextBoxName.Value
    Sheets("DataSheet").Range("A2").Value = TextBoxAge.Value   
    ' Close the form
    Unload Me
End Sub

' This is the event handler to reset the form (clear the fields and error messages)
Private Sub CommandButtonReset_Click()
    TextBoxName.Value = ""
    TextBoxAge.Value = ""
    LabelError.Caption = ""
End Sub

' This is the event handler to close the form
Private Sub CommandButtonClose_Click()
    Unload Me
End Sub

Explanation of the Code:

1. CommandButtonSubmit_Click:
   • This event is triggered when the user clicks the « Submit » button.
   • The first line clears any existing error message (LabelError.Caption = «  »).
   • It checks if the TextBoxName is empty. If it is, an error message is displayed, and the focus is set back to the TextBoxName.
   • Then, it checks if the TextBoxAge is empty or contains a non-numeric value. If it fails either check, an error message is displayed, and the focus is set to TextBoxAge.
   • If both fields pass the validation, a success message is shown (MsgBox), and the data is stored in an Excel worksheet (Sheets(« DataSheet »).Range(« A1 »).Value).
   • Finally, the form is closed using Unload Me.
2. CommandButtonReset_Click:
   • This event is triggered when the user clicks the « Reset » button.
   • It clears all fields (TextBoxName and TextBoxAge) and the error message (LabelError.Caption).
3. CommandButtonClose_Click:
   • This event is triggered when the user clicks the « Close » button.
   • It simply closes the form using Unload Me.

Additional Validation Examples:

You can add more validation checks depending on the type of data you are collecting. Here are a few examples:

Example 1: Email Validation (simple version):

If Not IsEmailValid(TextBoxEmail.Value) Then

    LabelError.Caption = « Please enter a valid email address. »

    TextBoxEmail.SetFocus

    Exit Sub

End If

A simple email validation function could look like this:

Function IsEmailValid(ByVal email As String) As Boolean

    Dim emailPattern As String

    emailPattern = « ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$ »

    IsEmailValid = email Like emailPattern

End Function

Example 2: Date Validation:

If Not IsDate(TextBoxDate.Value) Then

    LabelError.Caption = « Please enter a valid date. »

    TextBoxDate.SetFocus

    Exit Sub

End If

Example 3: Length Validation:

If Len(TextBoxName.Value) < 3 Then

    LabelError.Caption = « Name must be at least 3 characters long. »

    TextBoxName.SetFocus

    Exit Sub

End If

Conclusion:

With this approach, you can create a powerful and flexible data-entry form with validation in Excel VBA. You can easily extend the validation rules to meet your specific requirements, whether you’re collecting text, numbers, dates, or even more complex data types.