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.
Below is the syntax of the 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
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
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:
- How to Remove Macros from Excel? 3 Simple Methods
- How to Delete a Sheet in Excel Using VBA
- How to Unhide All Rows in Excel with VBA
- Using Application.GetSaveAsFilename in VBA in Excel (Examples)
- SetFocus in Excel VBA – How to Use it?
- How to Open Excel Workbook Using VBA
- Subscript Out of Range Error in VBA – How to Fix!