Microsoft Office Tutorials and References
In Depth Information
Monitoring with Application Events
Enabling Application-level events
To use Application -level events, you need to do the following:
Create a new class module.
Set a name for this class module in the Properties window under Name.
By default, VBA gives each new class module a default name like Class1 , Class2 , and so
on. You may want to give your class module a more meaningful name, such as clsApp .
In the class module, declare a public Application object by using the WithEvents
Public WithEvents XL As Application
Create a variable that you’ll use to refer to the declared Application object in the
It should be a module-level object variable declared in a regular VBA module (not in the
class module). For example:
Dim X As New clsApp
Connect the declared object with the Application object.
This step is often done in a Workbook_Open procedure. For example:
Set X.XL = Application
Write event-handler procedures for the XL object in the class module.
This procedure is virtually identical to that required to use events with an embedded
chart. See Chapter 18.
Determining when a workbook is opened
The example in this section keeps track of every workbook that is opened by storing information
in a comma-separated variable (CSV) text file. You can import this file into Excel.
I start by inserting a new class module and naming it clsApp . The code in the class module is
Public WithEvents AppEvents As Application
Private Sub AppEvents_WorkbookOpen (ByVal Wb As Excel.Workbook)