Stop Excel Events from Triggering when Running a Macro

Create a macro that will run without triggering events, such as the Change event, or Activate event, or BeforeSave event. This allows the macro to run without causing any event-driven macros to run.

Section:

Code

Example

Notes

Code

Turn Events Off

This prevents the event-driven macros from running.

Application.EnableEvents = False

Turn Alerts On

This re-enables events so any event-driven macros can run again.

Application.EnableEvents = True

Example

The main reason that you want to set EnableEvents to false is because events in Excel are almost always created to respond to a user action and not the action of another macro.

When you run a macro, you want to be able to do what you need to do in Excel without having to worry about event-driven macros causing unexpected issues, errors, or changes in the data.

In the example below, I have an event-driven macro that will generate a messagebox pop-up that says "hi" each time a different worksheet is selected in the workbook. However, I want to run a macro that will send the user to Sheet2 and I don't want them to see the pop-up message box that would normally appear.

Here is the macro I want to run:

Sub event_off_test()

Application.EnableEvents = False

'Code to run here
Sheets("Sheet2").Activate

Application.EnableEvents = True

End Sub

Here is the macro that I DO NOT want to run as a result of running the original macro:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

MsgBox "hi"

End Sub

The second macro is contained in ThisWorkbook and the first one is in Module1.

If you run the first macro as it is, the messagebox pop-up will not appear! That is due to the event-driven macros being turned-off.

However, if I comment-out the EnableEvents lines of code like this:

Then, when I go to run the macro, I will see the pop-up.

This is a simple example, but it illustrates quite well the annoyance that event-driven macros can cause when you want to run another macro without interference.

Notes

Always remember that you should turn the EnableEvents back on at the end of your macro; otherwise, the event-driven macros will not be able to run again. So, set the EnableEvents to false at the very top of the macro and set it to true at the very end.

In the sample file for this tutorial, I commented-out the EnableEvents lines so you can see what happens when you use that code and when you don't; just remove the single quote from the start of the line to enable that line of code and test it out.

Make sure to download the sample file for this tutorial to work with these examples in Excel.