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.
The following macro use the DisplayAlerts Property to temporarily disable Excel’s standard alerts and warning messages.
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
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.