Page 455 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Creating Advanced User Forms
Validating Data

At this point, the form is fully capable of capturing data from the user and inserting it into
the worksheet. The form also allows the user to edit the values already stored in the work-
sheet. The only limitation is that none of the data is validated for correctness.
For instance, it’s possible to enter an invalid date as part of the DateAdded field. Also, there
are no checks to ensure that the CustomerId value is numeric. Finally, it’s possible to enter the
wrong two-character state code. Here are some techniques that you can use to ensure that the
data is valid before it reaches your worksheet.

The first technique involves using the KeyPress event to ensure that the user can enter only a Chapter 20
particular type of information. For example, you could ensure that the user can only enter
numbers into the CustomerId control using this code:

Private Sub CustomerId_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < Asc("0”) Or KeyAscii > Asc("9”) Then
KeyAscii = 0

End If
End Sub

Tip Defining Events
Double-clicking the CustomerId control on the user form will automatically take you to the
CustomerId_Change event. If the event doesn’t exist, it will automatically be created. If you
want to handle a different event, simply choose the name of the event from the drop-down
list at the top of the code window and the Visual Basic Editor will automatically create a
blank event with the appropriate parameters.

Another approach involves using the Exit event. In the Exit event associated with a particular
control on the user form, you can determine if the user made an error and highlight the back-
ground to give the user a visual clue. You can also display a message box that contains a
description of the error using code like this:

Private Sub DateAdded_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(DateAdded.Text) Then
DateAdded.BackColor = &HFF&
MsgBox "Illegal date value"
Cancel = True
Else
DateAdded.BackColor = &H80000005
End If

End Sub

429