Disable Alerts and Warning Messages in Excel

5
883

When building macros there can be times when the standard Excel warning alerts need to be disabled in order for the macro to complete it’s intended function.

Disable DisplayAlerts Property

An example may be where you have developed a macro that needs to create a temporary worksheet to store some values and then before completing the macro you need to clean-up and delete that sheet. The standard Excel warning alert would be presented unless you include some code to disable that alert.
delete worksheet dialog box

The following macro use the DisplayAlerts Property to temporarily disable Excel’s standard alerts and warning messages.

Step 1

Open the workbook with the hidden worksheets and start the Visual Basic Editor (Alt + F11). Right Click in the project explorer window and select Insert > Module. Copy the following code into the new Module

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

Step 2

Note : The final step in the macro enables Excel alerts. This is done to ensure any subsequent unplanned actions are reported and handled by Excel.

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.