Microsoft Office Tutorials and References
In Depth Information
Examining Worksheet Events
End If
‘ It passed all the tests
EntryIsValid = True
End Function
The preceding technique works, but it can be rather tedious to set up. Wouldn’t it be nice if you
could take advantage of Excel’s data validation feature, yet ensure that the data validation rules
don’t get deleted if the user pastes data into the validation range? The next example solves the
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VT As Long
‘Do all cells in the validation range
‘still have validation?
On Error Resume Next
VT = Range(“InputRange”).Validation.Type
If Err.Number <> 0 Then
MsgBox “Your last operation was canceled.” & _
“It would have deleted data validation rules.”, vbCritical
End If
End Sub
This event procedure checks the validation type of the range (named InputRange ) that is
supposed to contain the data validation rules. If the VT variable contains an error, that means that
one or more cells in the InputRange no longer contain data validation. In other words, the
worksheet change probably resulted from data being copied into the range that contains data
validation. If that’s the case, the code executes the Undo method of the Application object
and reverses the user’s action. Then it displays the message box shown in Figure 19-7.
A nice side-benefit to using this procedure is that the Undo stack isn’t destroyed.
This example, named validate entry3.xlsm , is available on the companion
Search JabSto ::

Custom Search