vba turn off alerts in Excel
Q197. In Excel, how can I turn off Alerts using VBA?
For example, I have written a macro to automate a set of routine tasks so that no user intervention is required. However, since my automation involves deletion of WorkSheets (so, whenever code is executed to delete
WorkSheet, pop up message "Data may exist in sheet selectedů..") as well as file overwriting (pop up message confirming if file can be overwritten), user intervention is required. Is there a way to suppress these messages?
To do it in Excel, here is the answer:
- Option Explicit
- Sub SubRoutineSample()
- Application.DisplayAlerts = False
- ' complex
- ' macro
- ' function
- ' goes here
- Application.DisplayAlerts = True
- End Sub
a) Line 3 - Turn OFF Display Alerts when macro is running. This suppresses pop ups. This can be placed at the beginning of code or just before actions that induce pop up messages like WorkSheet deletion, file save etc.,
c) Line 8 - Turn ON Display Alerts after macro is executed.