Page 293 - Microsoft Office Excel 2003 Programming Inside Out

Understanding and Using Events

Note This setting is for the entire Excel application, so setting it to False will affect all
your open workbooks, not just the active workbook containing the code. Also, remember
that Excel doesn’t restore the setting when your code ends, so be sure to set it back to True
to reactivate the events.

The primary reason to disable events is to prevent an infinite loop of continuous events. For
example, let’s say you have developed a timesheet and the maximum number of hours an
employee is allowed to work is 40 hours per week. You can write the code to validate the cell
contents whenever data is entered into the cell containing the total hours of work for each
employee. In this example, you would monitor the Change event for the worksheet using a
procedure named Worksheet_Change. Your procedure would check the user’s entry to verify
that it is less than 40. If the entry exceeds 40, the procedure will display a message informing
the user that he or she has exceeded the allowed hours of work and then clear the entry in the
cell. The problem with this scenario is that when the cell contents are cleared, the VBA code
generates a new Change event, so the event is executed again. This is not what you want to
happen, so you need to disable events before you clear the cell, and then reactivate the events
so that you can monitor the next user entry. The following Worksheet_Change event displays
the required code to validate the users input in a range named Hours. For this example you
can replace the range name Hours with a specific cell address to test this code in any work-
book. Because this Event procedure refers to the worksheet level, the code needs to be added
to the module of the worksheet that you want to trigger the Change event.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("Hours")
If Intersect(Target, VRange).Value > 40 Then
MsgBox "The weekly hours cannot exceed 40."
Application.EnableEvents = False
ActiveCell.Value = ""
Application.EnableEvents = True
End If
End Sub

Note Application.EnableEvents = False will affect all open workbooks; however,
it does not affect events outside of the Excel Object Model. Events associated with ActiveX
controls and User Forms will continue to occur.

Workbook Events

Events for the Workbook object occur within a particular workbook. The Workbook events are
activated by default, but as mentioned in the previous section, they can be disabled by setting
the EnableEvents property to False. To display the event procedures for a workbook, start by Chapter 12
opening the Visual Basic Editor. Expand the desired project in the project window and dou­
ble-click the ThisWorkBook object to active it. All event procedures in the workbook will be
displayed in the code window on the right side of the screen.

267
Part 4: Advanced VBA