Sample code to check the entries in an UserForm Textbox. Download the sample file to test the code.
To create a UserForm requires some programming, and there are setup instructions here: Create an Excel UserForm.
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
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.
Those two strings are different, so it means that there was an "@" symbol found, and the address is valid.
However,
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
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
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.
Last updated: May 6, 2021 7:50 PM