Page 313 - Excel Progamming Weekend Crash Course

r540629 ch22.qxd 9/2/03 9:35 AM Page 288

288 Sunday Morning

Part 5: Writing the Data Validation Code

The verification code checks the data when the user clicks the Next or Done button. The
specific items that need to be checked are:

The First Name, Last Name, Address, and City fields are not blank.
A state is selected.
The zip code field contains five characters. Because input to this field has been
restricted to digits, this is all the verification that is required.

If the verification is successful, the data is entered in the worksheet, and the form is
cleared and displayed again for another entry. Alternatively, if the Done button was selected,
the form is closed. You can see that the verification is performed when the user clicks either
the Next or Done button. For this reason the verification code should not be placed in the
Click event procedure for a button, but rather in its own procedure. This procedure can then
be called from both the Done and the Next buttons’ Click event procedures.
Follow these steps to create the validation procedure:
1. Display the code-editing window for the user form.
2. Select Insert ➪ Procedure to open the Add Procedure dialog box.
3. Enter ValidateData as the procedure name; select Function under Type.
4. Click OK.

The validation code is placed in a function (instead of a sub procedure) so it
can return a value to the calling program: True if validation succeeds, False if
Note it fails.
The code for the validation procedure is shown in Listing 22-3. Note that in addition to
the code within the function, the return specifier As Boolean has been added to the first
line of the function. You should add the code in this listing to your program.

Listing 22-3 The Data Validation function

Public Function ValidateData() As Boolean

‘ Returns True if the data in the user form
‘ is complete, False otherwise. Displays a
‘ message identifying the problem.
If txtFirstName.Value = “” Then
MsgBox “You must enter a first name.”
ValidateData = False
Exit Function
End If
If txtLastName.Value = “” Then
MsgBox “You must enter a last name.”