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

Add to Favorites

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

f3852f16f10331c09120cadd0def390b.jpg

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

6113db589eb7b08eeb8b4166d5473c68.jpg

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:

65eece8befe523a556ff58bb71f64bc9.jpg

Here is what happens when I run it:

74d379658d27bacce1db0fa2e4b78265.jpg

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
Macro: Run a macro after a certain amount of time has passed since the Excel workbook was ope...
Run a Macro When a Specific Cell Changes in Excel
Tutorial: Run a macro in Excel when a specific cell is changed; this also covers when a cell within...
Create a Column Chart with a Macro in Excel
Macro: This macro adds a column chart to Excel. This is an easy to use macro that allows you to q...
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...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a...
Stop Excel Events from Triggering when Running a Macro
Tutorial: Create a macro that will run without triggering events, such as the Change event, or Activ...