98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Application.DisplayAlerts = False not working when saving
Application.DisplayAlerts = False not working when saving
Resolved · Low Priority · Version 2003
Helen has attended:
Excel VBA Advanced course
Application.DisplayAlerts = False not working when saving
Hi there
I've had this problem a few times with different projects and it means that I can't use my macro as it needs to run on a loop throughout the day.
I'm saving a csv file rather than and excel workbook but I get the Excel warning Do you want to save the changes you made to 'CheckElements.csv'.
I don't really care if it saves or not.
I've tried within the displayalerts lines:
activeworkbook.close savechanges:=false
activeworkbook.close savechanges:=true
activeworkbook.close false
activeworkbook.close true
activeworkbook.close save
Sub MakeCSV()
sPath = "\\UK-BASA-APP007\d$\Server9\TI_Load\"
sfile = "CheckElements.csv"
sMissSheet = "MissingElements"
On Error Resume Next
Kill sPath & sfile
On Error GoTo 0
Sheets(sMissSheet).Select
Sheets(sMissSheet).Copy
ChDir sPath
ActiveWorkbook.SaveAs Filename:=sPath & sfile, _
FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close False
Application.DisplayAlerts = True
End Sub
RE: Application.DisplayAlerts = False not working when saving
Hi Helen
Thanks for getting in touch. I've tried your code as is on Excel 2003 and other versions and it runs without any notifications or popups.
Can I suggest putting Application.DisplayAlerts = False before
ActiveWorkbook.SaveAs Filename:=sPath & sfile, _
FileFormat:=xlCSV, CreateBackup:=False
It's also worth stepping through (F8) to see which event fires the dialogue box so you can address that single line, as you have multiple save events.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Application.DisplayAlerts = False not working when saving
Hi Gary
I've had the application.displayalerts = false line all the way through directly before the line that is giving problems.
I've been stepping through it again and again with F8 trying all sorts of options.
I've managed to do it finally using
activeworkbook.saved = true
To force the workbook think of it as saved (although it really should have anyway as I've just saved it).
before I try to close it.
That finally worked, but application.displayalerts = false doesn't work in all cases.
Kind regards
Helen
RE: Application.DisplayAlerts = False not working when saving
Hi Helen
Thanks for your reply. I did some research and this forum thread sounds promising:
http://www.mrexcel.com/forum/excel-questions/228016-application-displayalerts- %3D-false-not-working.html
In particular it draws attention to volatile functions and updating cells in your workbooks.
It also mentions a line I'd not come across before that might help:
ThisWorkbook.Saved = True
I hope there's something in that thread that helps you.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 9 Apr 2014: Automatically marked as resolved.
Training information:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Apply Autosum with keyboard shortcutIf you press Alt and = at the same time, it applies autosum. |