Excel

How to Listen Application Events

In previous tutorials, we discussed Workbook events and Worksheet events. Those events occur for a particular workbook or for a particular worksheet which can be listen by programming their code modules in the VBA Project Explorer tree. You can use the Application-level events if you want to monitor events for the entire Excel application. Application-level events affect all available workbooks and worksheets in an Excel session. For example, the Workbook_Open event procedure is executed when the workbook in which the code resides is opened. If you want to run the same code in every workbook available, you have to copy the code to each workbook. Alternatively, you can use an application event, WorkbookOpen, which occurs when any workbook is opened.

But how can you program the Application-events as there is not Application code module exist in the VBA Project Explorer tree. To program Application level events you need to create an object variable that represents the Excel’s Application object and propagate its events, as shown in the code:

Dim WithEvents App As Application

WithEvents is a modifier which specifies that the declared variable refer to an instance of a class that can raise events. You can declare this modifier in ThisWorkbook code module or in a Class module.

Declaring WithEvents in ThisWorkbook

Open a workbook and press Alt+F11 to open the VBE. Declares the App variable in the ThisWorkbook code module and use the Workbook_Open() event procedure to set the App object variable to reference the current Excel window:

'ThisWorkbook
Option Explicit

Dim WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

And once this reference is set, it appears in the Object list box as shown in the figure:

Now, close the workbook (save changes) and then re-open it. The Workbook_Open is executed and load the Application events when you re-open the workbook. Write the following code, the App_NewWorkbook procedure executes everytime you create a new Workbook:

'ThisWorkbook
Option Explicit

Dim WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
 MsgBox Wb.Name & " was created"
End Sub

Declaring WithEvents in a Class Module

You also can create a separate code module to listen application-level events. All you need is to create a Class module. Click Insert and choose Class Module from the menu to insert a new Class module:

Set a meaningful name, such as AppClass, for the class module in the Properties window under Name and then add the following code in the module:

'AppClass
Option Explicit

Public WithEvents App As Application

The Application events are now available to the workbook, as shown in the figure:

Next, open ThisWorkbook code module and load AppClass class, and set the Application object reference for the class in Workbook_Open procedure:

'ThisWorkbook
Option Explicit

Dim Obj As New AppClass

Private Sub Workbook_Open()
 Set Obj.App = Application
End Sub

Now, close the workbook (save changes) and then re-open it. The Workbook_Open is executed and load the Application events when you re-open the workbook.

Example: This code disallows any Workbook to be closed:

'AppClass
Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
 Cancel = True
End Sub

3. Using standard module

  1. Insert a class module and declare Public WithEvents App As Application (See Using Class Module to Listen Application Events)
  2. Insert a standar module and create a variable that you'll use to refer to the declared Application object in the class module. For example: Dim Obj As New AppClass. You also need to create a procedure, for example InitApp (see below)
  3. Call the procedure (InitApp) created in the standard module when the Workbook opens (using Workbook_Open procedure in ThisWorkbook module)

Click Insert and choose Module from the menu to insert a standard module. Next, load the class Dim Obj As New AppClass which we created in Using Class Module, create a subroutine Sub InitApp() and set the Application object for the class in the newly created procedure:

'Standard Module
Option Explicit

Dim Obj As New AppClass

Sub InitApp()
 Set Obj.App = Application
End Sub

Figure: Standard Module

Next, open the ThisWorkbook code module and call the InitApp procedure (which you created in standard module) from the Workbook_Open procedure:

'ThisWorkbook
Option Explicit

Private Sub Workbook_Open()
 Call InitApp
End Sub

The Workbook_Open procedure calls the InitApp procedure when the workbook opens:

Example: write following code in AppClass module to disallow any Workbook to be closed:

'AppClass
Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
 Cancel = True
End Sub

Application events are not working

In our examples, we used the Workbook_Open to automatically load Application events when the workbook is first opened. So, for these events to take effect, it is necessary to close the workbook (where the code is resides) and reopen it.