Excel UserForm Textbox Code

Sample code to check the entries in an UserForm Textbox. Download the sample file to test the code.

Introduction to UserForms

To create a UserForm requires some programming, and there are setup instructions here: Create an Excel UserForm.

Check TextBox Entries

To collect good data with a UserForm, you might need to check the values that were entered in one or more of the textboxes on the form. Then, if the data looks valid, it can be copied to the data storage sheet.

The verification code could be added to the form's OK button, or to the AfterUpdate event for the text box.

In this example, the code will:

--check for an @ character in the Email TextBox

--count the number of characters in the Part Code TextBox

Example 1 - Check for a Specific Character

Use code to check the value that was entered in a TextBox. For example, in an email address box, you could make sure that an @ symbol was included. To do that, the Replace function is used.

To test the string in the email address TextBox, by replacing any "@" symbol with an empty string.

If the email address is: john@home.com
the test string would be: johnhome.com

Those two strings are different, so it means that there was an "@" symbol found, and the address is valid.

However,

If the email address is: johnAThome.com
the test string would be: johnAThome.com

Those two strings are the same, so it means that no "@" symbol found, and the address is not valid.

If Me.txtEmail.Value = _
     Replace(Me.txtEmail.Value,"@","") Then
   MsgBox "Please enter a valid email address"
   GoTo ExitHandler
End If

Example 2 - Check the Number of Characters

Use code to check the number of characters that were entered in a TextBox. For example, in a part code box, you could make sure that 10 characters were entered. To do that, the Len function is used.

If Len(Me.txtPart.Value) <> 10 Then
   MsgBox "Please enter a 10 character part code"
   GoTo ExitHandler
End If

Download the Sample File

To see how the user form works, you can download the sample UserForm with TextBoxes file.

The zipped file is in xlsm format (Excel 2007 and later), and contains macros. Enable macros to test the code.