Using Application.EnableEvents in VBA in Excel (Examples)

by

Application.EnableEvents is a property in Excel VBA where you can specify whether you want events to take place when the VBA code is running or not.

An event is usually attached to an object. VBA Events could include things such as activating a worksheet or saving a workbook or double-clicking on a cell.

Excel VBA allows you to create event-specific code, where you can specify what code to run in case the user double-clicks on a cell. In this case, this would be the double-click event.

Similarly, you can have a code that is run as soon as a new worksheet is activated in the workbook.

Now, these events are useful, but sometimes you may not want them to work.

For example, if you’re running a code that will activate and loop through all the open worksheets one by one, you may not want to execute the event code (in case you have it) when each sheet is activated.

In such cases, you can set the Application.EnableEvents property to false, making sure the events are turned off when the code is running.

Now that you have a better understanding of what Application.EnableEvents does (I hope), let’s see the syntax and some examples.

Application.EnableEvents Syntax

Below is the syntax of the Application.Events

 Application.Events

where, the application is the object (when using this in Excel VBA, Excel is the application), and Events is the property.

So you can set the Application.Events property to either True or False.

Application.Events = True

or

Application.Events = False

When you set this to TRUE, events will run as usual in Excel VBA, and if you set this to FALSE, events would stop working (for the whole VBA).

One caveat here is that if you set this to FALSE, remember to set it back to TRUE again (which is the default and expected behavior of this property)

Now let’s see an example of how to use it

Application.EnableEvents Example

Below is a code where the main code is flanked by the Application.EnableEvents property.

Sub ExampleCode()
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End Sub

In the above code, the ThisWorkbook.Save is flanked by Application.EnableEvents properties being set first to FALSE and then to TRUE.

When you run this code, in case there are any events associated with the workbook getting saved, it will not be run. And since you have set the property back to TRUE in the last line, this will not impact other macros in your workbook.

Note: In case you’re calling other subroutines from one of the subroutines that use Application.EnableEvents, you need to remember to make sure you turn it back on. Also, remember that when you run this code and in case of Excel finds any errors while the code is running, the last line will not be executed (and Application.EnableEvents will not get back to TRUE).

Also read: 100 Useful Excel VBA Macro Codes Examples

Application.EnableEvents Not Working

Remember that Application.EnableEvents would not work with Userforms events and controls.

Here is an article that talks about some of the disadvantages of using Application.EnableEvents and an alternative way to handle events in Excel VBA.

So this is all that you needed to know about the Application.EnableEvents property in Excel VBA.

Hope you found this tutorial useful!

Other Excel tutorials you may like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment