Excel VBA – Save Workbook without “Are you Sure” pop up

Recently I’ve been working to “automate” my life, well my work reporting at least!  Why spend 3 hours on a Sunday reporting the weeks data if you can automate this.  So I set out to create a “Master Macro” that would sequentially open all of my weekly reports, update them, save, close and move onto the next one.

An issue I hit with this was that using the save command Activeworkbook.Save it would pull up a prompt to the user saying words to the effect of “are you sure you want to save changes?”  I wanted to suppress this as I don’t want to keep checking back on the macro and having to click yes periodically…  This is how

 Application.DisplayAlerts = False
'Your Code goes here'
 Application.DisplayAlerts = True

The Application.Display Alerts property disables prompts and alerts when a macro is running, if the prompt requires a choice Excel will always choose the default.

Personally I always put this within my code just around the save command, Then if there is anything wrong with my code / that weeks data set which would force a real prompt I will get to see it as the only code with display alerts set to false is my save command.

Like this –

Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True

I hope you find this helpful, leave a comment!

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.