How to Create Application Level Events in Excel VBA?

#tocreateapplicationl #vbamacros

In this article, you will learn how to create Application Level events in Excel VBA. 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

Step 1: Create an Event Object in A Class Module

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.

Now, in project explorer , you have to insert a class module and name it as MyAppEvents.
After that, you need to define an event variable of Application type with keyword WithEvents.

 Private WithEvents myApp As Application

You have to initialize this event in class_initialize() subroutine.
From the drop-down on the left, you need to select class.
Then, on the top-right drop-down, you have to select initialize.

Private Sub Class_Initialize()
 Set myApp = Application
End Sub

Now, you need to define the events you want to use.
From the top-left drop-down, you have to select the event object.
After that, all the available event handling procedures will be available to you in the top-right drop-down menu.
You can select whichever you require and define what you want to do when that event triggers.
Here, you need to use the SheetActivate Event.

Private Sub myApp_SheetActivate(ByVal Sh As Object)
 MsgBox ActiveWorkbook.Name & "-" & Sh.Name
End Sub

Step2: Create an Event Starter Subroutine in Normal Module

Firstly, you have to insert a normal module.
Then, you need to define a variable of the class you have created.

 Private AppE As MyAppEvents

Now, you have to initialize it in a subroutine , and you can name it as you want.

Private Sub StartEvents()
    Set AppE = New MyAppEvents
End Sub

After that, you need to save the code by selecting it and then close the window.

You need to Run this code using the F5 key.
Now, you can assign this macro to a button on the worksheet if you want to start the events from the worksheet.
Finally, whenever you will switch sheets , a message box will appear with the name of the workbook and the sheet , as long as the code containing workbook is open.

Switch Over:

Switching VBA Application Events On and Off

Firstly, once you run the macro in the normal module , it will trigger always until you close the workbook that contains the events.
Then, if you want them to switch on and off , there are two ways.
- Nullify the Event Object
- Set EnableEvents to False

Nullifying The Event Object

In a separate subroutine , you have to set the event object to Nothing.

Private Sub StopEvents()
 Set AppE = Nothing
End Sub

Then, once you run this code , the events will stop working.
Now, you can put it in a button on the worksheet to stop the events.
After that, you will have two buttons to start and stop these specific events.
Finally, it will just stop events created by the AppE object.

Set EnableEvents to False

Firstly, this method is to disable the events.
Then, to make all events uncatchable we set the EnableEvents property of Application class to False.

Private Sub StopEvents() 
 Application.EnableEvents= False
End Sub

Now, the above code will disable all the events and also the default excel events.
After that, it will, not work until you start them again.
Even if you run the StartEvents() subroutine (above), the event will not work.
To make all events work again , you have to set EnableEvents property again to True.

Private Sub StartEvents()
 Application.EnableEvents = True
 Set AppE = New MyAppEvents
End Sub

You need to save the code by selecting it and then close the window.
Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
You need to choose the Macros option in the Code section.

Finally, you will get the output in the Microsoft Excel.

Starting Custome Application Events every time Workbook Opened

Firstly, if you are developing a tool for an end-user , you want the events to work automatically.
In that case, you can put the event starter in the Workbook object with Workbook_open() event, instead of in a normal module.
Finally, this will make your event object initialized as soon as you open the workbook that contains the events.

Wrap-Up

We hope that this short tutorial gives you guidelines on how to create Application Level events in Excel VBA. ** 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:

Get n8n VPS hosting 3x cheaper than a cloud solution

Get fast, easy, secure n8n VPS hosting from $4.99/mo at Hostinger. Automate any workflow using a pre-installed n8n application and no-code customization.

Start now