Application object events

 1999-08-21    Events    0    133

Events for the Application object occurs when a workbook is created, opened or when a sheet in any workbook is changed.
If you want to write event procedures for the Application-object, you will have to create a new object with the keyword WithEvents in a class module.
After the new class module is created you can attach macros to the following events:

NewWorkbook
SheetActivate
SheetBeforeDoubleClick
SheetBeforeRightClick
SheetCalculate
SheetChange
SheetDeactivate
SheetSelectionChange
WindowActivate
WindowDeactivate
WindowResize
WorkbookActivate
WorkbookAddinInstall
WorkbookAddinUninstall
WorkbookBeforeClose
WorkbookBeforePrint
WorkbookBeforeSave
WorkbookDeactivate
WorkbookNewSheet
WorkbookOpen

Create eventmacros for the Application object

  1. Start the Visual Basic Editor (press Alt+F11 in Excel).
  2. Select the desired project in the Project-window.
  3. Insert a new class module by selecting the menu Insert, Class Module.
  4. Activate the new class module and rename it, e.g. AppEventClass
  5. Copy and paste the example macros below to the new class module.
Public WithEvents Appl As Application

Private Sub Appl_NewWorkbook(ByVal Wb As Workbook)
    ' your code here
    MsgBox "A new workbook is created!"
End Sub

Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    ' your code here
    MsgBox "A workbook is closed!"
End Sub

Private Sub Appl_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
    ' your code here
    MsgBox "A workbook is printed!"
End Sub

Private Sub Appl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' your code here
    MsgBox "A workbook is saved!"
End Sub

Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook)
    ' your code here
    MsgBox "A workbook is opened!"
End Sub
After you have finished editing the event macros for the Applicationobject, you have to add some code to the module ThisWorkbook to activate the new event macros:

Dim ApplicationClass As New AppEventClass

Private Sub Workbook_Open()
    Set ApplicationClass.Appl = Application
End Sub
After the Workbook_Open procedure has run, the events attached to the Application object are activated.