Before close - not always deleting sheet- WHY??

[Patti]

In the ThisWorkbook module I have:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Sheets("SheetName1").Delete
ThisWorkbook.Sheets("SheetName2").Delete
On Error GoTo 0
Application.DisplayAlerts = True
End Sub

When the user closes the file, the following behavior occurs:

If user says NO to save changes, the sheets are deleted.
If user says YES to save changes, the sheets are deleted. (good so far....)

BUT, if the user makes a change, saves it, then says NO to the save prompt
when closing, the sheets are *not* deleted.

First of all, I don't understand why the user would get the "save changes?"
prompt right after saving on their own (which it does even if they do
nothing else). Second, I don't understand why the sheets aren't deleted in
this situation.

If anyone can explain, I would really appreciate it! (Using xl2002, no
other subs in ThisWorkbook module.)

Regards,

Patti

 

[Tom Ogilvy]

The workbook must be saved to store the file with the sheets deleted.
Perhaps your testing is flawed, but your statement

If user says NO to save changes, the sheets are deleted.

would only be true if the workbook closing was halted in some way.


so the reason they are not deleted in your last example is that the workbook
is not saved. The user gets the prompt because your event changes the
workbook before the point of evaluating whether to show the dialog or not.

 

[Patti]

I appreciate the reply Tom.

I think I get it...when I said I had "no other subs in ThisWorkbook module"
I was really trying communicate that I wasn't doing any type of Before Save
or thisworkbook.saved =, etc. But, I do delete a commandbar right before
the sheets. I moved that snippet to below the delete sheets code, and it
seems fine now.

It's all in the details isn't it??

You guys just amaze me with your ability to provide accurate answers despite
the faulty explanations we give sometimes!

Regards,

Patti