Trapping Application Events

You can use a class module to trap application events. Most of these events are the same as the workbook events, but they apply to all open workbooks, not just the particular workbook that contains the event procedures. For example, in a workbook there is a BeforePrint event that is triggered when you start to print anything in that workbook. At the application level, there is a WorkbookBeforePrint event that is triggered when any open workbook starts to print.

To see what application events are available, you first insert a class module into your project. The class module can have any valid module name. The one shown in Figure 16-4 has been named CAppEvents. You then type in the following variable declaration at the top of the module:

Public WithEvents xlApp As Application

The object variable name, xlApp, can be any valid variable name, as long as you use it consistently in code that refers to the class module, as a property of the class. The WithEvents keyword causes the events associated with the application object to be exposed. You can now choose xlApp from the dropdown at the top left of the module and then use the drop-down at the top right to see the event list, as shown in Figure 16-4.

Figure 16-4

Choose the WorkbookBeforePrint event and extend the event procedure presented in Chapter 9, using the following code in CAppEvents:

Private Sub xlApp_WorkbookBeforePrint(ByVal Wbk

As Workbook, _

Cancel As


Dim wks As Worksheet

Dim sFullFileName As String

Dim sCompanyName As String

With Wbk

'Define footer data

sCompanyName = "Execuplan Consulting"

sFullFileName = .FullName

'Process each worksheet

For Each wks In .Worksheets

With wks.PageSetup

.LeftFooter = sCompanyName

.CenterFooter = ""

.RightFooter = sFullFileName

End With

Next wks

End With

End Sub

Unlike sheet and workbook class modules, the event procedures you place in your own class modules do not automatically function. You need to create an instance of your class module and assign the Application object to the xlApp property of the new object. The following code must be set up in a standard module:

Public xlApplication As CAppEvents

Sub TrapApplicationEvents()

'Create instance of class module Set xlApplication = New CAppEvents

'Assign the Excel Application object to the xlApp property Set xlApplication.xlApp = Application

End Sub

All you need to do now is execute the TrapApplicationEvents procedure. The WorkbookBeforePrint event procedure will then run when you use any Print or Preview commands, until you close the workbook containing the event procedure.

It is possible to terminate application event trapping during the current session. Any action that resets module-level variables and public variables will terminate application event processing, because the class module instance will be destroyed. Actions that can cause this include editing code in the VBE and executing the End statement in VBA code.

If you want to enable application event processing for all Excel sessions, you can place your class module and standard module code in Personal.xlsb and execute TrapApplicationEvents in the Workbook_Open event procedure. You can even transfer the code in TrapApplicationEvents to the Workbook_Open event procedure. However, you must keep the Public declaration of xlApplication in a standard module.

To illustrate, you can place the following code in the declarations section of a standard module:

Public xlApplication As CAppEvents

You can place the following event procedure in the ThisWorkbook module:

Private Sub Workbook_Open()

Set xlApplication = New CAppEvents Set xlApplication.xlApp = Application End Sub

0 0

Post a comment