Page 304 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

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
than another.

Turning on Application Event Monitoring

In the previous sections, we discussed how to create Worksheet 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:
1 Create a new class module.
2 Set a name for the class module in the Properties window under Name. For example:
AppEventClass.
3 In the class module, declare a public Application object using the WithEvents keyword.
For example:

Public WithEvents Appl As Application
4 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."
End Sub

Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox "You closed the workbook."
End Sub

Private Sub Appl_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox "You are printing the workbook."
End Sub

Private Sub Appl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal _
SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "You saved your workbook."
End Sub

Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "You opened a workbook."
Chapter 12
End Sub
278
Part 4: Advanced VBA