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.
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
Trim$removes leading and trailing spaces.Lenchecks whether the resulting string is empty.MsgBoxdisplays an error message.SetFocusreturns the cursor to the TextBox.Exit Substops processing invalid input.
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.
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 |
|---|---|
| 85 | Valid |
| 0 | Valid |
| 100 | Valid |
| -5 | Invalid |
| 105 | Invalid |
| ABC | Invalid |
| Empty input | Invalid |
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
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
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
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
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
12. Input Validation Best Practices
- Validate mandatory fields before saving data.
- Trim unwanted leading and trailing spaces from text fields.
- Use
IsNumericbefore 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
SetFocusto direct users to the invalid field. - Stop execution with
Exit Subafter 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
IsDateenforces 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.
- Validate that an employee name is not empty.
- Validate that salary contains a numeric value.
- Reject negative salary values.
- Validate student marks between 0 and 100.
- Ensure a department is selected from a ComboBox.
- Ensure a student is selected from a ListBox.
- Validate a joining date using
IsDate. - Create a basic email format check.
- Validate a ten-digit phone number.
- Check whether an employee ID already exists.
- Display an appropriate error message for each invalid field.
- Build a complete form that validates data before saving.
- 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$andLento check required text fields. - Use
IsNumericbefore converting numeric input. - Validate numeric ranges according to the application's rules.
- Check ComboBox and ListBox selections before reading them.
- Use
IsDateto 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.