Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Input Validation in Excel VBA UserForms

Learn how to validate TextBox values, check required fields, verify numeric input, validate ComboBox selections, display meaningful error messages, and prevent invalid data from being saved to Excel worksheets.

1. What Is Input Validation?

Input validation is the process of checking user-entered information before processing or saving it. A UserForm may collect names, phone numbers, email addresses, dates, quantities, salaries, or other information. Validation helps ensure that the values meet the application's requirements.

For example, an employee registration form should not save an empty employee name, a non-numeric salary, or a negative salary when negative values are not allowed.

User enters information VBA validates the input Valid Input Process or save the data Invalid Input Display an error message Only acceptable data should be processed
Key idea: Validate input before saving it to a worksheet or using it in calculations.

2. Why Is Input Validation Important?

Validation Type Purpose Example
Required Field Ensures mandatory information is entered. Employee name cannot be blank.
Numeric Validation Checks whether a value is numeric. Salary must be numeric.
Range Validation Checks whether a value is within allowed limits. Marks must be between 0 and 100.
Selection Validation Ensures an item has been selected. A course must be selected.
Format Validation Checks whether data follows an expected format. Email address or phone number.
Duplicate Validation Checks whether a record already exists. Employee ID must be unique.

3. Validate Required Fields

Required-field validation checks whether the user has entered a value in a mandatory field. The Trim$ function removes leading and trailing spaces, while Len checks the length of the result.

Example 1: Validate an employee name

Create a TextBox named txtEmployeeName and a CommandButton named cmdSave.

Private Sub cmdSave_Click()

    Dim employeeName As String

    employeeName = Trim$(Me.txtEmployeeName.Value)

    If Len(employeeName) = 0 Then
        MsgBox "Please enter the employee name.", vbExclamation
        Me.txtEmployeeName.SetFocus
        Exit Sub
    End If

    MsgBox "Employee name is valid.", vbInformation

End Sub

Explanation

  1. Trim$ removes leading and trailing spaces.
  2. Len checks whether the resulting string is empty.
  3. MsgBox displays an error message.
  4. SetFocus returns the cursor to the TextBox.
  5. Exit Sub stops processing invalid input.
Important: A field containing only spaces should normally be treated as empty when validating names.

4. Validate Numeric Input

Use IsNumeric to check whether a value can be interpreted as a number before converting it with CDbl, CLng, or another conversion function.

Example 2: Validate salary

Private Sub cmdCheckSalary_Click()

    Dim salary As Double

    If Len(Trim$(Me.txtSalary.Value)) = 0 Then
        MsgBox "Please enter the salary.", vbExclamation
        Me.txtSalary.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(Me.txtSalary.Value) Then
        MsgBox "Salary must be numeric.", vbExclamation
        Me.txtSalary.SetFocus
        Exit Sub
    End If

    salary = CDbl(Me.txtSalary.Value)

    MsgBox "Entered salary: " & Format(salary, "0.00"), vbInformation

End Sub

The code checks for empty input first, verifies that the value is numeric, and then converts it to a Double.

Tip: Perform the numeric check before converting the value. This prevents common type-conversion errors when users enter text instead of numbers.

5. Validate a Numeric Range

Range validation ensures that a number falls within an acceptable minimum and maximum. For example, marks may be restricted to the range 0 through 100.

Example 3: Validate student marks

Private Sub cmdCheckMarks_Click()

    Dim marks As Double

    If Len(Trim$(Me.txtMarks.Value)) = 0 Or _
       Not IsNumeric(Me.txtMarks.Value) Then

        MsgBox "Enter valid numeric marks.", vbExclamation
        Me.txtMarks.SetFocus
        Exit Sub

    End If

    marks = CDbl(Me.txtMarks.Value)

    If marks < 0 Or marks > 100 Then
        MsgBox "Marks must be between 0 and 100.", vbExclamation
        Me.txtMarks.SetFocus
        Exit Sub
    End If

    MsgBox "Marks are valid.", vbInformation

End Sub

Sample test cases

Input Expected Result
85Valid
0Valid
100Valid
-5Invalid
105Invalid
ABCInvalid
Empty inputInvalid

6. Validate ComboBox and ListBox Selections

Users may leave a ComboBox or ListBox unselected. Validate the selection before using its value or saving the record.

Example 4: Validate a ComboBox

Create a ComboBox named cmbDepartment and configure it with a list of departments.

Private Sub cmdSaveDepartment_Click()

    If Me.cmbDepartment.ListIndex = -1 Then
        MsgBox "Please select a department.", vbExclamation
        Me.cmbDepartment.SetFocus
        Exit Sub
    End If

    MsgBox "Selected Department: " & _
           Me.cmbDepartment.Value, vbInformation

End Sub

Example 5: Validate a ListBox

Private Sub cmdShowStudent_Click()

    If Me.lstStudents.ListIndex = -1 Then
        MsgBox "Please select a student.", vbExclamation
        Me.lstStudents.SetFocus
        Exit Sub
    End If

    MsgBox "Selected Student: " & _
           Me.lstStudents.Value, vbInformation

End Sub
Note: The examples above assume single selection. A multi-select ListBox requires checking its individual selected items.

7. Validate Date Input

Date validation checks whether a user-entered value can be interpreted as a date. Excel's interpretation of date strings may depend on regional settings, so use a clearly defined date format when building applications.

Example 6: Validate a date of joining

Private Sub cmdCheckDate_Click()

    Dim joiningDate As Date
    Dim inputValue As String

    inputValue = Trim$(Me.txtJoiningDate.Value)

    If Len(inputValue) = 0 Then
        MsgBox "Please enter the joining date.", vbExclamation
        Me.txtJoiningDate.SetFocus
        Exit Sub
    End If

    If Not IsDate(inputValue) Then
        MsgBox "Enter a valid date.", vbExclamation
        Me.txtJoiningDate.SetFocus
        Exit Sub
    End If

    joiningDate = CDate(inputValue)

    MsgBox "Joining Date: " & _
           Format(joiningDate, "dd-mmm-yyyy"), vbInformation

End Sub
Important: IsDate checks whether VBA recognizes a value as a date, but it does not enforce a single text format. For strict date formats, parse the components explicitly before converting.

8. Basic Email Format Validation

A basic email validation can check for common structural mistakes, such as a missing @ symbol or a missing dot in the domain. This is only a simple format check and cannot prove that an email address exists.

Example 7: Check a basic email format

Private Sub cmdCheckEmail_Click()

    Dim emailAddress As String
    Dim atPosition As Long
    Dim dotPosition As Long

    emailAddress = Trim$(Me.txtEmail.Value)

    atPosition = InStr(1, emailAddress, "@")
    dotPosition = InStrRev(emailAddress, ".")

    If Len(emailAddress) = 0 Or _
       atPosition <= 1 Or _
       dotPosition <= atPosition + 1 Or _
       dotPosition >= Len(emailAddress) Then

        MsgBox "Enter an email address in a valid basic format.", _
               vbExclamation

        Me.txtEmail.SetFocus
        Exit Sub

    End If

    MsgBox "The email format passed the basic check.", vbInformation

End Sub

This example is suitable for demonstrating string checks. For production applications, use a more complete format validation approach and verify the address separately when necessary.

9. Validate a Phone Number

Phone numbers should generally be treated as text because they may contain a leading zero or country code. For this example, assume the application requires exactly ten digits.

Example 8: Validate a ten-digit phone number

Private Sub cmdCheckPhone_Click()

    Dim phoneNumber As String
    Dim i As Long
    Dim isValid As Boolean

    phoneNumber = Trim$(Me.txtPhone.Value)

    If Len(phoneNumber) <> 10 Then
        MsgBox "Phone number must contain exactly 10 digits.", _
               vbExclamation
        Me.txtPhone.SetFocus
        Exit Sub
    End If

    isValid = True

    For i = 1 To Len(phoneNumber)

        If Mid$(phoneNumber, i, 1) < "0" Or _
           Mid$(phoneNumber, i, 1) > "9" Then

            isValid = False
            Exit For

        End If

    Next i

    If Not isValid Then
        MsgBox "Phone number must contain digits only.", vbExclamation
        Me.txtPhone.SetFocus
        Exit Sub
    End If

    MsgBox "Phone number format is valid.", vbInformation

End Sub
Tip: This example checks a ten-digit format only. Real phone-number requirements can vary by country and should be defined by the application.

10. Check for Duplicate Employee IDs

Duplicate validation prevents a new record from using an ID that already exists in the destination worksheet. The following example searches column A in the Employees worksheet.

Example 9: Validate a unique employee ID

Create a TextBox named txtEmployeeID and a worksheet named Employees. Existing employee IDs should be stored in column A, starting at row 2.

Private Sub cmdCheckEmployeeID_Click()

    Dim ws As Worksheet
    Dim employeeID As String
    Dim lastRow As Long
    Dim foundCell As Range

    employeeID = Trim$(Me.txtEmployeeID.Value)

    If Len(employeeID) = 0 Then
        MsgBox "Enter an employee ID.", vbExclamation
        Me.txtEmployeeID.SetFocus
        Exit Sub
    End If

    Set ws = ThisWorkbook.Worksheets("Employees")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "Employee ID is available.", vbInformation
        Exit Sub
    End If

    Set foundCell = ws.Range("A2:A" & lastRow).Find( _
        What:=employeeID, _
        After:=ws.Cells(lastRow, "A"), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "Employee ID is available.", vbInformation
    Else
        MsgBox "This employee ID already exists.", vbExclamation
        Me.txtEmployeeID.SetFocus
    End If

End Sub
Note: This procedure checks for duplicates but does not save a record. Perform the duplicate check again immediately before saving if other processes could add records between the check and the save.

11. Practical Project: Employee Form Validation

Create an employee registration form with the following controls:

Control Name Validation Requirement
TextBox txtEmployeeName Cannot be empty.
TextBox txtEmployeeID Cannot be empty or duplicated.
ComboBox cmbDepartment A department must be selected.
TextBox txtSalary Must be numeric and non-negative.
CommandButton cmdSave Validate all fields before saving.

Example 10: Validate all required fields

The following procedure validates the required fields and salary before proceeding. It assumes that the department ComboBox is populated and the worksheet named Employees exists.

Private Sub cmdSave_Click()

    Dim employeeName As String
    Dim employeeID As String
    Dim department As String
    Dim salary As Double

    employeeName = Trim$(Me.txtEmployeeName.Value)
    employeeID = Trim$(Me.txtEmployeeID.Value)
    department = Trim$(Me.cmbDepartment.Value)

    If Len(employeeName) = 0 Then
        MsgBox "Enter the employee name.", vbExclamation
        Me.txtEmployeeName.SetFocus
        Exit Sub
    End If

    If Len(employeeID) = 0 Then
        MsgBox "Enter the employee ID.", vbExclamation
        Me.txtEmployeeID.SetFocus
        Exit Sub
    End If

    If Me.cmbDepartment.ListIndex = -1 Then
        MsgBox "Select a department.", vbExclamation
        Me.cmbDepartment.SetFocus
        Exit Sub
    End If

    If Len(Trim$(Me.txtSalary.Value)) = 0 Or _
       Not IsNumeric(Me.txtSalary.Value) Then

        MsgBox "Enter a valid salary.", vbExclamation
        Me.txtSalary.SetFocus
        Exit Sub

    End If

    salary = CDbl(Me.txtSalary.Value)

    If salary < 0 Then
        MsgBox "Salary cannot be negative.", vbExclamation
        Me.txtSalary.SetFocus
        Exit Sub
    End If

    MsgBox "All required fields passed validation.", vbInformation

    ' Continue with duplicate checking and saving here.

End Sub
Expected behavior: If any required field is invalid, the procedure displays an error and stops. When all required fields pass, the code can continue to duplicate checking and saving.

12. Input Validation Best Practices

  • Validate mandatory fields before saving data.
  • Trim unwanted leading and trailing spaces from text fields.
  • Use IsNumeric before numeric conversion.
  • Check minimum and maximum values where required.
  • Validate ComboBox and ListBox selections.
  • Use consistent date and phone-number rules.
  • Display clear and specific error messages.
  • Use SetFocus to direct users to the invalid field.
  • Stop execution with Exit Sub after invalid input.
  • Check duplicate identifiers before saving.
  • Do not rely only on visual formatting to guarantee valid data.
  • Validate again at the point where data is saved or processed.

13. Common Mistakes to Avoid

  • Converting a TextBox value to a number before validating it.
  • Checking whether a field is empty without trimming spaces.
  • Allowing negative values when the business rule prohibits them.
  • Using a ComboBox value without checking whether an item was selected.
  • Assuming IsDate enforces a specific date format.
  • Assuming a basic email check proves that an email address exists.
  • Treating phone numbers as numbers instead of text.
  • Failing to stop the procedure after validation fails.
  • Checking for duplicate records only after saving.
  • Testing only valid input and ignoring edge cases.

14. Practice Exercises

Complete these exercises using VBA UserForms. Test each procedure with valid values, invalid values, and empty input.

  1. Validate that an employee name is not empty.
  2. Validate that salary contains a numeric value.
  3. Reject negative salary values.
  4. Validate student marks between 0 and 100.
  5. Ensure a department is selected from a ComboBox.
  6. Ensure a student is selected from a ListBox.
  7. Validate a joining date using IsDate.
  8. Create a basic email format check.
  9. Validate a ten-digit phone number.
  10. Check whether an employee ID already exists.
  11. Display an appropriate error message for each invalid field.
  12. Build a complete form that validates data before saving.
  13. Test the form with blank values, spaces, letters, decimals, and boundary values.

Important Points to Remember

  • Input validation checks data before processing or saving it.
  • Use Trim$ and Len to check required text fields.
  • Use IsNumeric before converting numeric input.
  • Validate numeric ranges according to the application's rules.
  • Check ComboBox and ListBox selections before reading them.
  • Use IsDate to perform basic date validation.
  • Phone numbers should generally be stored as text.
  • Check duplicate IDs before saving records.
  • Use clear error messages and return focus to the invalid field.
  • Test edge cases as well as valid inputs.

Summary

Input validation is an essential part of Excel VBA UserForms. It ensures that required fields are completed, numeric values are valid, selections are made, dates follow the required rules, and duplicate identifiers are detected.

By combining validation functions, conditional statements, meaningful messages, and checks before saving, you can create reliable UserForms that reduce data-entry errors and protect the quality of information stored in Excel worksheets.