Prevent Excel Alerts and Messages Appearing While Running a Macro in Excel

How to stop an Excel alert window or message box from appearing while running a macro. This is particularly useful when running a macro that needs to close a workbook and you don't want the "Save" dialogue box to appear.

Sections:

Code

Example Close Workbook

Notes

Code

Turn Alerts Off

Application.DisplayAlerts = False

Turn Alerts On

Application.DisplayAlerts = True

Example Close Workbook

The number one reason to use this is to allow Excel to open a workbook, get data from it, and close it without saving changes or seeing a pop-up message box asking to save the file.

Here is an example that will close the workbook:

Sub test_save()

Application.DisplayAlerts = False


'Code to run here
ActiveWorkbook.Close


Application.DisplayAlerts = True

End Sub

When I run the above macro, the workbook will close without any notices, messages, or warnings.

However, if I comment-out the lines with DisplayAlerts and run it, the process is different.

Here is the commented-out code:

Here is what happens when I run it:

That window appears and the user has to click a button to proceed. This is not a very user-friendly setup, especially if a novice user is trying to use a macro that you created and they don't know what is going on.

Notes

This is a very helpful piece of code to include in your macros. Keep this information in the back of your mind so you can use it when needed.

In the sample file for this tutorial I commented-out every line of code so you can uncomment what you want to test it.

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