Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before close - not always deleting sheet- WHY??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before close - not always deleting sheet- WHY??
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. -- Regards, Tom Ogilvy "Patti" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before close - not always deleting sheet- WHY??
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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "Patti" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code to close an excel sheet without saving it .(via msgbox) | Excel Worksheet Functions | |||
how to setup a excel sheet to be protect after save and close file | Excel Discussion (Misc queries) | |||
Excel creating TMP with each save but not deleting on close | Excel Discussion (Misc queries) | |||
Close Hidden Data Sheet | Excel Programming | |||
Hanging on sheet close | Excel Programming |