Soliciting data from users is a common use for a Word UserForm, but a typo, a correctly spelled but invalid value, or even a control left blank can render your document unusable. Fortunately, you can include a bit of VBA code to check input values for specific attributes. In this article, we’ll look at simple examples of validating input values before committing them to the document.
This article’s example form and controls assume you have basic knowledge of Word’s UserForm object. If you don’t know how to create a UserForm, consider reading 10 steps to creating a Word userform for addressing letters before you continue. To simplify the examples, we’ll change only the properties necessary to support the technique at hand. You can work with your own UserForm or download the demonstration .docm or .doc file.
Note: Don’t copy and paste the code from this web page, because some web-based characters will return errors.
Validating levels
You can think of validating data in terms of opening a door, and that door can be:
- Open
- Ajar
- Closed
Names present the most common use of an open control; the door is open and any name can pass through to your document. Displaying the input value and asking the user to confirm the value before committing it can help, but beyond that, you can do little to validate this type of input–anything the user enters can pass through the door.
An ajar control lets values through if they fit. The value must pass a few tests before the code allows it to pass through. For instance, you might check US ZIP code entries by ensuring that each character is a numeric digit and that there are five (or nine) characters. Unfortunately, you can’t verify that the entered ZIP code is the right ZIP code for a corresponding address.
A closed control forces the user to choose an item from a list. In this way, you protect your data from typos and invalid entries because the user never enters a new or unique value from the keyboard. Only when the user selects a value from the available options does Word open the door and let the value in. Unfortunately, the user can still select the wrong item.
SEE: How to use Excel’s Data Validation feature to prevent data entry mistakes
Open example
An open control is an open door for allowing bad data into your document. Use them only when there’s no other option. As I mentioned, names are a good example because programmatically restricting names is a rabbit hole you don’t want to explore. Figure A shows a simple UserForm with two text box controls and a command button. (Refer to Table A for form and control properties.)
Figure A
Use simple text box controls to accept new name values.
Table A
At the very least, you can parrot the user’s input and ask for confirmation before committing the value to its purpose, giving the user the opportunity to fix typos. To expand the example, we’ll also apply a business rule that requires both a first and last name. We can do both via the command button that commits the form’s data to the document. To accomplish this, double-click the command button and add the code in Listing A to its module.
Listing A
Private Sub cmdSendtoDocument_Click()
'Last ditch validation before committing input values to document.
Dim booConfirmation As Boolean
'Check for first and last name values; both are required.
If Len(txtLastName) = 0 Or Len(txtFirstName) = 0 Then
MsgBox "Both first and last names are required.", vbOKOnly, "Input Error"
If Len(txtLastName) = 0 Then
txtLastName.SetFocus
Else
txtFirstName.SetFocus
End If
Exit Sub
End If
'Display name so user can check and confirm.
booConfirmation = MsgBox("Is the name " & txtFirstName & " " _
& txtLastName & " correct?", vbYesNo)
If booConfirmation Then
'This is where you'd add code that commits the input values to the document.
Exit Sub
Else
txtLastName.SetFocus
End If
End Sub
If either Len() function returns 0, VBA displays the message shown in Figure B, selects the blank control so the user can remedy the error, and then exits the procedure. (Click Run Macro or press [F5] to run the UserForm and enter test values.) If both controls contain text, the next snippet displays the name, as shown in Figure C. Clicking Yes commits the name to the document. Right now, the code does nothing else because there’s no real purpose behind the example. Clicking No returns focus to txtLastName, allowing the user to change either name; the code isn’t intuitive enough to know which name (first or last) needs correction.
Figure B
Display an error message if the user leaves either name control blank.
Figure C
Ask the user to confirm the name.
We used the command button to check the name values because we’re considering two values rather than one. It would be next to impossible to coordinate checks with the user’s intentions. Despite the business rule we applied, a user can still enter gibberish for one or both names. In the next example, you can tighten things up a bit.
Ajar example
The ZIP code example we discussed earlier is great for illustrating an ajar control. We’ll use code that allows only five numeric characters. To accomplish this, add a text box to the UserForm, name the control txtZIP, and set its TabIndex property to 2. Then, double-click txtZIP and add the code shown in Listing B to its module.
Listing B
Private Sub txtZIP_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Validate ZIP code entries. Each must have five numeric digits.
Dim bytLength As Byte
Dim booNumeric As Boolean
'Must be 5 AND True to be a valid ZIP code.
bytLength = Len(txtZIP)
booNumeric = IsNumeric(txtZIP)
If bytLength <> 5 Then
MsgBox "You must enter a five-digit ZIP Code.", vbOKOnly, "ZIP Code error"
txtZIP.SetFocus
Exit Sub
ElseIf booNumeric = False Then
MsgBox "Your ZIP Code value contains alpha characters.", vbOKOnly, _
"ZIP Code errors"
txtZIP.SetFocus
Exit Sub
End If
End Sub
Exiting the control triggers the event. Figure D shows the results of entering alpha characters and too many (it could also be too few) characters. We’ve closed the door a bit and only some values will make it through. However, there’s no way to guarantee that the five digits the user enters is a real ZIP code or that it’s the right ZIP code for the corresponding record.
Figure D
The code rejects invalid entries.
SEE: How to update and alphabetize a validation control list programmatically in Excel
Closed example
When possible, use a closed control. That way, you know the actual value is correct even if the user chooses the wrong one. List and option controls support this need best. To illustrate a closed control, we’ll add a combo box control and populate its list with three items: Yes, No, and Undecided.
Start by adding a combo box control, name it cboClosed, set its TabIndex to 3, and set its MatchRequired property to True. (By default, this control will accept keyboard entries.) Then, double-click the UserForm’s background (not the combo box) and add the code shown in Listing C to the UserForm’s module. When you run the form, this procedure will populate the combo’s list.
Listing C
Private Sub UserForm_Initialize()
'Populate list.
With cboClosed
.AddItem "Yes"
.AddItem "No"
.AddItem "Undecided"
End With
End Sub
The user can’t enter a value from the keyboard; the control accepts only an item from its list. You could stop right here, but there’s still something you need to consider. Although the list has three items, there’s a fourth option–a null value. As is, the user could choose nothing.
Null values aren’t wrong, but you must accommodate them if you allow them. Failing to do so can have unexpected consequences, such as changing the results of expressions and functions. In addition, they can be overlooked in sorted or filtered datasets. Those problem are unlikely in a Word document, but at the very least, you might have an empty field in the middle of your document. To add a check for null values to the control’s Exit procedure, open the control’s module and enter the code shown in Listing D.
Listing D
Private Sub cboClosed_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Check for selected item; user must select an item from the list.
If cboClosed = "" Then
MsgBox "You must choose an item from the list.", vbOKOnly, _
"Input Error"
Cancel = True
cboClosed.SetFocus
End If
End Sub
If a user leaves the control empty, this code displays the informational message shown in Figure E. The door is closed until the user chooses an appropriate value from the list. However, there’s no way to guarantee that the user selects the right item for the record.
Figure E
This control forces you to choose a value from the combo box control’s list.
Validating is a start
When validating user input, you must consider the possible responses and be as flexible as possible, while also protecting the validity of the data and thus, the information you glean from it. From this discussion, have you picked up on something important? You can do your best, but a lot depends on the user; despite your best efforts, the user can still make mistakes. Data validation is necessary and goes a long way toward protecting your data, but it isn’t a panacea for errors.
You’ll want to choose the most efficient (least intrusive) flow. By that I mean, where and when do you validate data? In the first example, you click the command button to initiate the validation code; in the second and third examples, exiting the control triggers the code. You can validate an entry as soon as it’s entered or you can validate data after the data entry task is complete but before committing those values to the document. More often than not, a single UserForm will use both.
Be sure to include appropriate error handling; the examples have none. The door analogy is my own and not a technical reference that you’ll find in the properties or methods.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.
Also read…
- 10 tips for synching Outlook with mobile devices
- Office Q&A: How to save time formatting Word tables
- Six tips for using Excel sheet groups in easy (but unusual) ways
- Configure Outlook’s Calendar view to suit your work routine
