UEU-co logo

Application Events


Previous Page

Next Page

Application Events

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.

Table 19-4: EVENTS RECOGNIZED BY THE APPLICATION OBJECT
Open table as spreadsheet

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:

  1. Create a new class module.

  2. 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.

  3. In the class module, declare a public Application object by using the WithEvents keyword. For example:

    Public WithEvents XL As Application

  4. 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

  5. Connect the declared object with the Application object. This is often done in a Workbook_Open procedure. For example:

    Set X.XL = Application

  6. 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 Image from book log workbook open.xlsm, is available on the companion CD-ROM.

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.

Image from book
Figure 19-9: This workbook uses a class module to monitor all Application-level events.

CD-ROM 

This example is available on the companion CD-ROM. The file is named Image from book application event tracker.xlsm.

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.

Previous Page

Next Page

Leave a Reply


Time limit is exhausted. Please reload the CAPTCHA.

Categories

apply_now Pepperstone Group Limited