How to Create Application Level Events in Excel VBA

The Application level events work on the entire application(Excel in this reference). But activating the application event is not as easy as creating Workbook or Worksheet Events. But I assure you that it is not that tricky as well. In this article, we will learn how to create and use the Application Event in Excel VBA in a few easy steps.

Step 1: Create an Event Object in A Class Module

To create an event object, we need to use the class module.

Insert a class module. Name it as you want. I named it MyAppEvents.
Define an event variable of Application type with keyword WithEvents.
```
Private WithEvents myApp As Application
```
Initialize this event in class_initialize() subroutine.
From the drop-down on the left, select class. Afterward, on the top-right drop-down, select initialize.
```
Private Sub Class_Initialize()
 Set myApp = Application
End Sub
```
Now define the events you want to use. From the top-left drop-down, select the event object. All the available event handling procedures will be available to you in the top-right drop-down menu. Select whichever you require and define what you want to do when that event triggers.
I use the SheetActivate Event. Whenever a user will switch among sheets of any open workbook, it will show the name of the workbook and the sheet user has selected.
```
Private Sub myApp_SheetActivate(ByVal Sh As Object)
 MsgBox ActiveWorkbook.Name & "-" & Sh.Name
End Sub
```

You can define as many events you want to define here.

The events won't start working immediately. This was the first step. Since it is a class module, we need to create an object of it to make events activate.

Now we only need to start these events from a normal subroutine.

Step2: Create an Event Starter Subroutine in Normal Module

Now we have an event class. We just need to create an object of that class and initialize it. Once we run this subroutine, all the defined events in the event class will start working.

Insert a normal module.
Define a variable of the class you have created.
```
Private AppE As MyAppEvents
```

Initialize it in a subroutine. You can name it as you want.

Private Sub StartEvents()

    Set AppE = New MyAppEvents

End Sub

Run this code using the F5 key. Optionally, you can assign this macro to button on the worksheet if you want to start the events from the worksheet.

And it is done. Now 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.

Was it tough? I don't think so. Let me know what you think in the comment section below.

Switching VBA Application Events On and Off

Once you run the macro in the normal module, it will trigger always until you close the workbook that contains the events. But you may want them to switch them on and off at your will. There are two ways to do this.

Nullify the Event Object
Set EnableEvents to False

1. Nullifying The Event Object

In a separate subroutine, set the event object to Nothing

Private Sub StopEvents()
 Set AppE = Nothing
End Sub

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

2. Set EnableEvents to False

The second method is to disable the events. To make all events uncatchable we set the EnableEvents property of Application class to False.

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

The above code will disable all the events. Even the default excel events. They 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 will have to set EnableEvents property again to True.

So if you want your events to work every time you start the events, add this line of code in the sub.

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

Starting Custome Application Events every time Workbook Opened

If you are developing a tool for end-user, you may 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. This will make your event object initialized as soon as you open the workbook that contains the events.

So yeah guys, this how you can use the application event in excel. Let me know if this was explanatory enough and helped you understand Application-Level Events in Excel VBA in the comments section below. If you have something to add to this, write that down too. You can ask your queries related to this article or any other excel VBA related topic in the comments section below.

Download the working file below:

Related Articles:

The Events in Excel VBA | There are seven types of Events in Excel. Each event deals in different scope. Application Event deals with on workbook level. Workbook on sheets level. Worksheet Event on Range level.

The Worksheet Events in Excel VBA |The worksheet event are really useful when you want your macros run when a specified event occurs on the sheet.

Workbook events using VBA in Microsoft Excel | The workbook events work on the entire workbook. Since all the sheets are part of the workbook, these events work on them too.

Prevent an automacro/eventmacro executes using VBA in Microsoft Excel | To prevent the run of auto_open macro use the shift key.

Chart object events using VBA in Microsoft Excel | The Charts are complex objects and there are several components that you attached to them. To make the Chart Events we use the Class module.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make your work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.