DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

How To Create event macros for the Application object using VBA in Excel?

#tocreateeventmacrosf #vbamacros

In this tutorial, you will learn how to create event macros for the application object using VBA in Microsoft Excel. Let’s see them below!! Get the official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Example

  • Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
  • Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic
Select Visual Basic

  • Now, you have to click on the Insert and select Module.

Insert a module
Insert a module

  • After that, you need to copy and paste the code given below. 
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
Enter fullscreen mode Exit fullscreen mode
  • You have to save the code by selecting it and then close the window.

Save the Code

  • Then, 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
Enter fullscreen mode Exit fullscreen mode
  • Now, you have to save the code by selecting it and then close the window.

Another Code

  • Again, you need to go to the Excel Spreadsheet , and click on the Developer Tab.
  • Now, you have to choose the Macros option in the Code section.

Choose Macro option
Choose Macro option

  • Finally, after you run the Workbook_Open procedure , the events attached to the Application object are activated.

Wrap-Up

We hope that this tutorial gives you guidelines on how to create event macros for the application object using VBA in Microsoft Excel. ** ** Please leave a comment in case of any queries, and don’t forget to mention your valuable suggestions as well. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *Read more on Excel Formulas *!!

Read Also:

Top comments (0)