Stop Excel Events from Triggering when Running a Macro

Add to Favorites

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

f79aa976e59fa72a6386ebb223894a5f.png

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

1afd4cffc4e6b2bb4d1ece2811ac639b.jpg

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

06cabfee60db537d9f10eaf2f81d13df.jpg

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:

24b2bddd0c1718305ac1b694d4387b7c.jpg

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

f2c8bb845f0bd7ab7d447d1b792033da.jpg

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
Disable Calculation of Cells when Running a Macro in Excel
Tutorial: How to stop formulas and functions from updating in Excel when running a macro. This can s...
Prevent Excel Alerts and Messages Appearing While Running a Macro in Excel
Tutorial: How to stop an Excel alert window or message box from appearing while running a macro. Thi...
Run a Macro when a User Does Something in the Worksheet in Excel
Tutorial: How to run a macro when a user does something in the worksheet, everything from selecting ...
Run a Macro when a User Does Something in the Workbook in Excel
Tutorial: How to run a macro when a user does something within the Workbook in Excel, such as openi...
Run a Macro at Set Intervals in Excel
Tutorial: Run a macro every 30 seconds, 1 minute, 10 minutes, 1 hour, etc.; this method allows you t...
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
Macro: Automatically run an Excel macro at a certain time. This allows you to not have to worry a...