Microsoft Office Tutorials and References
In Depth Information
Examining Worksheet Events
The Worksheet_Change procedure creates a Range object (named VRange ) that represents
the worksheet range that is validated. Then it loops through each cell in the Target argument,
which represents the cell or cells that were changed. The code determines whether each cell is
contained in the range to be validated. If so, it passes the cell as an argument to a custom
function ( EntryIsValid ), which returns True if the cell is a valid entry.
If the entry isn’t valid, the EntryIsValid function returns a string that describes the problem,
and the user is informed via a message box (see Figure 19-6). When the message box is
dismissed, the invalid entry is cleared from the cell, and the cell is activated. Notice that events are
disabled before the cell is cleared. If events weren’t disabled, clearing the cell would produce a
Change event that causes an endless loop.
Figure 19-6: This message box describes the problem when the user makes an invalid entry.
The EntryIsValid function procedure is shown here:
Private Function EntryIsValid(cell) As Variant
‘ Returns True if cell is an integer between 1 and 12
‘ Otherwise it returns a string that describes the problem
If Not WorksheetFunction.IsNumber (cell) Then
EntryIsValid = “Non-numeric entry.”
If CInt(cell) <> cell Then
EntryIsValid = “Integer required.”
‘ Between 1 and 12?
If cell < 1 Or cell > 12 Then
EntryIsValid = “Valid values are between 1 and 12.”