Why Prompted To Save Again?

  • Thread starter Thread starter Izar Arcturus
  • Start date Start date
I

Izar Arcturus

Has anyone else encountered this? Why does this happen?
What can I do so that I can still delete the sheets that I
need to and not have to save twice?

Well, after a bit of testing, it seems I have determined
why - when the user goes to close the Excel file produced
by my marco - that the user is prompted to save "changes"
again after the macro had already used SaveAs to get the
user to save the file before the macro finished running.

These inexplicable "changes" do not need to be saved again
after the macro is done, and the user is closing the Excel
file; when I get rid of a series of delete statements
which occur near the end of the macro but before the
SaveAs statement. The lines look like the following:

Application.DisplayAlerts = False
Worksheets(2).Delete
Worksheets(3).Delete
Worksheets(4).Delete
Application.DisplayAlerts = True

It's as if the deleting of these sheets does not finish
before the SaveAs has finished or even the macro has
finished.

I have even tried peppering the code after the SaveAs line
with a few Save statements. I have also employed the
suggestion received from my last post to include the
following line:

ActiveWorkbook.Saved = True

But this does not seem the be the solution either. Please
let me know you thoughts on this. Thank you.
 
I do not know why, but if you only have one sheet to
delete close to the end of you macro - you're fine. If you
want to delete more than one page you will have to
save "changes" when you close the file after the macro is
done.

After some tests I discovered that if I make a sub like
this:

Private Sub Deleter()
Application.DisplayAlerts = False
Worksheets(ScratchData).Delete
Worksheets(ScratchNames).Delete
Worksheets(ScratchCodesNTotCommit).Delete
Application.DisplayAlerts = True
End Sub

and put all the deleting action in there as you see, then
Excel is fine with that and will not ask the user to
save "changes" when the new file is closed.

If someone who works for Microsoft in designing Excel
could look into this, it would be nice if this oddity
could be changed.
 
Izar said:
It's as if the deleting of these sheets does not finish
before the SaveAs has finished or even the macro has
finished.
Click to expand...

Or it could be that the deleted sheets contain worksheet formulas whose
values change after the SaveAs. By deleting the sheets you have
deleted the formulas which were marking the workbook as not saved.

Just a thought.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
Hello Bill,

I generally do not use worksheet functions in the macros I
write. This particular macros did have a VLookup worksheet
function on one of the pages that was being deleted at the
end of the macro - in fact it referred to one of the other
sheets that was also deleted at the end of the macro.

But, for the sake of science I included code in the macro
that copied the results of the VLookup and pasted only the
values back on top of the cells that employed the VLookup,
so as to get rid of all worksheet functions before the end
of the macro and the sheet deletes.

Even without any worksheet functions, if the three sheet
deletes(that sounds lyrical) were in the main sub where
the SaveAs also occurred shortly after, then there
were "changes" that still needed to be saved when the
Excel file was closed - closed after the macro had just
saved the file during execution of code.

I am not sure what effect calling a Private Sub has on
Excel but, again, when I put the three sheet deletes in a
separate Private Sub, Excel understands that those sheets
have been deleted and Excel is okay with the programmatic
SaveAs and there are no "changes" that need to be saved
when the file is closed later.

Am I the only one that has experienced this? Can you
reproduce this in a macro of your own? If so, it seems
that this is something that is a disadvantage for coders
and users of Excel macros and could(should?) be changed by
Microsoft. Please share your further thoughts on this.
Thank you.

-Izar Arcturus
 
Back
Top