Tutorial Details
Downloadable Files: Excel File
Introduction to Programming Macros in Excel
First Steps
Getting and Inputting Data
Adding Logic to Macros
Loops
UDF- User Defined Functions
Speeding Up Macros
Security

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

Add to Favorites
Author:

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File