Microsoft Office Tutorials and References
In Depth Information
Examining Worksheet Events
This example, named make formulas bold.xlsm , is available on the companion
A potentially serious side-effect of using a Worksheet_Change procedure is that doing
so may effectively turn off Excel’s Undo feature. Excel’s Undo stack is destroyed
whenever an event procedure makes a change to the worksheet.
Monitoring a range to validate data entry
Excel’s data validation feature is a useful tool, but it suffers from a potentially serious problem.
When you paste data to a cell that uses data validation, the pasted value not only fails to get
validated, but it also deletes the validation rules associated with the cell! This fact makes the data
validation feature practically worthless for critical applications. In this section, I demonstrate how
you can use the Change event for a worksheet to create your own data validation procedure.
The companion CD-ROM contains two versions of this example. One (named validate
entry1.xlsm ) uses the EnableEvents property to prevent cascading Change events;
the other (named validate entry2.xlsm ) uses a Static variable. See “Disabling
events,” earlier in this chapter.
The Worksheet_Change procedure that follows is executed when a user changes a cell. The
validation is restricted to the range named InputRange . Values entered into this range must be
integers between 1 and 12.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant
Set VRange = Range(“InputRange”)
If Intersect(VRange, Target) Is Nothing Then Exit Sub
For Each cell In Intersect(VRange, Target)
ValidateCode = EntryIsValid(cell)
If TypeName(ValidateCode) = “String” Then
Msg = “Cell “ & cell.Address(False, False) & “:”
Msg = Msg & vbCrLf & vbCrLf & ValidateCode
MsgBox Msg, vbCritical, “Invalid Entry”
Application.EnableEvents = False
Application.EnableEvents = True