applicationdisplayalerts false

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum 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

resolvedResolved · 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 courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Apply Autosum with keyboard shortcut

If you press Alt and = at the same time, it applies autosum.

View all Excel hints and tips


Server loaded in 0.08 secs.