Turn off warning messages in MS Office applications

When using Office Automation in your Visual Basic application,
you'll often want to turn off MS Office prompts. For instance,
when deleting an Excel worksheet via code, you probably don't want
Excel to ask the user if they really want to delete the worksheet.
Or in Access, when you run an Action query, chances are you don't
want Access to ask the user's permission before deleting records.

You're probably aware that in Microsoft Access, to turn off such
warnings, you use the

DoCmd.SetWarnings = False

statement. This command turns off internal program warnings. The
DoCmd object, however, is a throwback to the days when all the
Office applications used different internal programming languages.
As a result, it doesn't work in any of the other Office applications.

To achieve the same behavior in the other Office applications,
use the application's DisplayAlerts property. As you can guess,
this property determines how a particular Office application shows
its alert messages. However, while each Office application other
than Access uses this property, they implement it in slightly different
ways.

For example, in Excel this property accepts a simple Boolean value
that turns the display messages on and off. With this in mind,
to delete the active worksheet via code, you'd use

Public Sub DeleteWorksheet()
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End Sub

Notice that we set the alerts back on after we turned them off.
That's because Excel doesn't do so automatically when it completes
the code execution. This behavior is consistent throughout Office.

Word uses a slightly different version of the same property. Instead
of accepting a Boolean value, Word uses three Long constants: wdAlertsNone,
wdAlertsAll and wdAlertsMessageBox. The first two settings are
fairly self-explanatory. The last setting tells Word to display
only standard message box alerts.

Under normal conditions, the code statement:

ActiveDocument.Close

would generate a message box asking if you wanted to save the document
in question. Using the DisplayAlerts property, the following code

Application.DisplayAlerts = wdAlertsNone
ActiveDocument.Close
Application.DisplayAlerts = wdAlertsAll

would immediately open the SaveAs dialog box without the initial
prompt.