Application Level Events

Application-level events affect all open workbooks in an Excel session. They require a class module to access them (similar to the class module used to access events for embedded chart events). Follow these steps to create the class module:

1. Insert a class module.

2. Rename the module to cl_AppEvents.

3. Enter the following line of code in the class module: Public WithEvents AppEvent As Application

The application events are now available to the workbook, as shown in Figure 8.7. They are accessed in the class module rather than in a standard module.

4. Insert a standard module.

5. Enter the following lines of code in the standard module:

Dim myAppEvent As New cl_AppEvents Sub InitializeAppEvent()

Set myAppEvent.AppEvent = Application End Sub

These lines initialize the application to recognize application events. The procedure must be run once per session (use Workbook_Open to automate this).

Figure 8.7

Application events are now available through the class module.

Figure 8.7

Application events are now available through the class module.

The following application-level events are now available for applying code to all open workbooks.

■ NewWorkbook

■ SheetActivate

■ SheetBeforeDoubleClick

■ SheetBeforeRightClick

■ SheetCalculate

■ SheetChange

■ SheetDeactivate

■ SheetFollowHyperlink

■ SheetSelectionChange

■ WindowActivate

■ WindowDeactivate

■ WindowResize

■ WorkbookActivate

■ WorkbookAddinInstall

■ WorkbookAddinUninstall

■ WorkbookBeforeClose

■ WorkbookBeforePrint

■ WorkbookBeforeSave

■ WorkbookDeactivate

■ WorkbookNewSheet

■ WorkbookOpen

0 0

Post a comment