Categories
Excel

Application.DisplayAlerts: Stop Warning Prompts

If you want to record a macro in Excel without being interrupted by warning prompts, you can use the DisplayAlerts property of the Application object. This property controls whether Excel displays certain alerts and messages while a macro is running.

One of the many drawbacks of recorded macros is that, although they’re pretty good at mimicking just about any command, they tend to forget your responses to prompts. Delete a worksheet and you’re prompted for confirmation; run a macro for the same and you’ll still be prompted. Let’s turn off those prompts:

  1. Select Views » Macros or Developer » Macros (ALT+F8 or Option+F8) to bring up a list of your macros.
  2. Make sure “All Open Workbooks” is selected in the “Macros In:” box’s pull-down menu.
  3. Select the macro you’re interested in and click the Edit button.
  4. Put the cursor before the very first line of code-the first line without an apostrophe in front of it, that is-and prepend the following:
Application.DisplayAlerts = False

At the very end of your code, append the following:

Application.DisplayAlerts = True

Your macro should look something like this:

Sub Macro1()
'
' Macro1 Macro
'

'
 Application.DisplayAlerts = False
 ActiveSheet.Delete
 Application.DisplayAlerts = True
End Sub

Note that you’ve turned alerts back on at the end of your macro to reenable standard Excel prompts while working in Excel. Leave this out, and you’ll see no alerts at all, not even those that might have been good to include.

If your macro does not complete for any reason-a runtime error, for instance-Excel might never get to the line of code that turns alerts back on. If this happens, it’s probably wise to quit and restart Excel to set things back to the way they were.

Now you know how to use Excel without prompts. Be aware, though, that these prompts are there for a reason. Make sure you fully understand the purpose of a prompt before summarily turning it off.

Understanding Workbooks and Worksheets: