Create eventmacros for the Application object using VBA in Microsoft Excel

Start the Visual Basic editor.
Select the desired project in the Project-window.
Insert a new class module by selecting the menu Insert | Class Module.
Activate the new class module and rename it, e.g. AppEventClass
Copy and paste these example macros 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 Application object,
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 you run the Workbook_Open procedure, the events attached to the Application object are activated.

Comments

Leave a Reply to Harix Cancel reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.