Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
In earlier sections, I discuss Workbook events and Worksheet events. Those events are monitored for a particular workbook. If you want to monitor events for all open workbooks or all worksheets, you use Application -level events.
| Note | Creating event handler procedures to handle Application events always requires a class module and some setup work. |
Table 19-4 lists the Application events with a brief description of each.
| Event | Action That Triggers the Event |
|---|---|
| AfterCalculate [*] | A calculation has been completed and no outstanding queries exist. |
| NewWorkbook | A new workbook is created. |
| SheetActivate | Any sheet is activated. |
| SheetBeforeDoubleClick | Any worksheet is double-clicked. This event occurs before the default double-click action. |
| SheetBeforeRightClick | Any worksheet is right-clicked. This event occurs before the default right-click action. |
| SheetCalculate | Any worksheet is calculated (or recalculated). |
| SheetChange | Cells in any worksheet are changed by the user or by an external link. |
| SheetDeactivate | Any sheet is deactivated. |
| SheetFollowHyperlink | A hyperlink is clicked. |
| SheetPivotTableUpdate | Any pivot table is updated. |
| SheetSelectionChange | The selection changes on any worksheet except a chart sheet. |
| WindowActivate | Any workbook window is activated. |
| WindowDeactivate | Any workbook window is deactivated. |
| WindowResize | Any workbook window is resized. |
| WorkbookActivate | Any workbook is activated. |
| WorkbookAddinInstall | A workbook is installed as an add-in. |
| WorkbookAddinUninstall | Any add-in workbook is uninstalled . |
| WorkbookAfterXMLExport | An XML file has been exported. |
| WorkbookAfterXMLImport | An XML file has been imported, or an XML data connection has been refreshed. |
| WorkbookBeforeClose | Any open workbook is closed. |
| WorkbookBeforePrint | Any open workbook is printed. |
| WorkbookBeforeSave | Any open workbook is saved. |
| WorkbookBeforeXMLExport | An XML file is about to be exported, or an XML data connection is about to be refreshed. |
| WorkbookBeforeXMLImport | An XML file is about to be imported. |
| WorkbookDeactivate | Any open workbook is deactivated. |
| WorkbookNewSheet | A new sheet is created in any open workbook. |
| WorkbookOpen | A workbook is opened. |
| WorkbookPivotTableCloseConnection | An external data source connection for any pivot table is closed. |
| WorkbookPivotTableOpenConnection | An external data source connection for any pivot table is opened. |
| WorkbookRowSetComplete [*] | The user drills through the recordset or invokes the rowset action on an OLAP PivotTable. |
| WorkbookSync | A workbook that is part of a Document Workspace is synchronized with the copy on the server. |
|
[*] This event was introduced in Excel 2007. | |
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 might 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 keyword. For example:
Public WithEvents XL As Application
-
Create a variable that you will use to refer to the declared Application object in the class module. This should be a module-level object variable declared in a regular VBA module (not in the class module). For example:
Dim As New clsApp
-
Connect the declared object with the Application object. This 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.
| CROSS-REFERENCE | 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. This file can be imported 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) Call UpdateLogFile(Wb) End Sub
This code declares AppEvents as an Application object with events. The AppEvents_ WorkbookOpen procedure will be called whenever a workbook is opened. This event handler procedure calls UpdateLogFile and passes the Wb variable, which represents the workbook that was opened. I then added a VBA module and inserted the following code:
Dim AppObject As New clsApp Sub Init() ' Called by Workbook_Open Set AppObject.AppEvents = Application End Sub Sub UpdateLogFile(Wb) Dim txt As String Dim Fname As String txt = Wb.FullName txt = txt & "," & Date & "," & Time txt = txt & "," & Application.UserName Fname = Application.DefaultFilePath & "\logfile.csv" Open Fname For Append As #1 Write #1, txt Close #1 MsgBox txt End Sub
Notice at the top that the AppObject variable is declared as type clsApp (the name of the class module). The call to Init is in the Workbook_Open procedure, which is in the code module for ThisWorkbook . This procedure is as follows :
Private Sub Workbook_Open() Call Init End Sub
The UpdateLogFile procedure opens a text file - or creates it if it doesn't exist. It then writes key information about the workbook that was opened: the filename and full path , the date, the time, and the username.
The Workbook_Open procedure calls the Init procedure. Therefore, when the workbook opens, the Init procedure creates the object variable.
| CD-ROM | This example, named |
Monitoring Application-level events
To get a feel for the event-generation process, you might find it helpful to see a list of events that get generated as you go about your work.
I created an application that displays (in a UserForm) each Application -level event as it occurs (see Figure 19-9). You might find this helpful in learning about the types and sequence of events that occur.
| CD-ROM | This example is available on the companion CD-ROM. The file is named |
The workbook contains a class module with 21 procedures defined, one for each Application -level event. Here's an example of one of them:
Private Sub XL_NewWorkbook(ByVal Wb As Excel.Workbook) LogEvent "NewWorkbook: " & Wb.Name End Sub
Each of these procedures calls the LogEvent procedure and passes an argument that consists of the event name and the object. The LogEvent procedure follows:
Sub LogEvent(txt) EventNum = EventNum + 1 With UserForm1 With .lblEvents .AutoSize = False .Caption = .Caption & vbCrLf & txt .Width = UserForm1.FrameEvents.Width - 20 .AutoSize = True End With .FrameEvents.ScrollHeight = .lblEvents.Height + 20 .FrameEvents.ScrollTop = EventNum * 20 End With End Sub
The LogEvent procedure updates the UserForm by modifying the Caption property of the Label control named lblEvents . The procedure also adjusts the ScrollHeight and ScrollTop properties of the Frame named FrameEvents , which contains the Label. Adjusting these properties causes the most recently added text to be visible while older text scrolls out of view. You can also adjust the vertical size of this UserForm. It uses the technique described in Chapter 15.