Microsoft Office Tutorials and References
In Depth Information
The Object Browser displays a list of matching items. Events are indicated with a small
yellow lightning bolt. From this list, you can see which objects support the Change event.
Notice how the list is divided into three columns: Library , Class , and Member . The match for
the item you are searching for might appear in any of these columns. The name of an event
or term belonging to one library or class might be the same as that for another belonging
to a different library or class, although they probably don’t share the same functionality.
When clicking each item in the Object Browser list, check the status bar at the bottom of
the list for the syntax. You might find that one class or library treats an event differently
Turning on Application Event Monitoring
In the previous sections, we discussed how to create Wo rksheet events and Workbook events.
Those events are for a particular workbook. If you need to monitor events for the entire Excel
application, use Application -level events. To use Application events, you must enable event
monitoring. Follow these steps to activate Application event monitoring:
Create a new class module.
Set a name for the class module in the Properties window under Name. For example:
In the class module, declare a public Application object using the WithEvents keyword.
Public WithEvents Appl As Application
To test the application event procedure, enter the following examples in the new class
module after the public variable:
Private Sub Appl_NewWorkbook(ByVal Wb As Workbook)
MsgBox "You created a new workbook."
Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox "You closed the workbook."
Private Sub Appl_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox "You are printing the workbook."
Private Sub Appl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal _
SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "You saved your workbook."
Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "You opened a workbook."