Stop the save message!

robfo0 · May 2nd, 2002, 05:28 PM

OK, a problem: I have a macro which goes through my workbook, and deletes some sheets, and deletes a module in my workbook. This all works fine. But, in my before close event, i have a piece of code which saves the workbook, thus preventing the Save Question box from coming up (well, supposed to). The problem is, when i run my code which deletes the module and sheets, and then close the workbook, the save question STILL comes up! (if i do NOT run the code which deletes, there is NO save message). The big problem here is that IF the user for some reason clicks "no" to the save question, it corrupts the excel file, and i can no longer open it!

I do not want to use the code "application.displayalerts=false" because there may be other workbooks open which i DO need alerts.

Can anyone think of any solutions? I would post my code, but theres a ton of it. let me know if you really need it, thanks.

zacemmel · May 2nd, 2002, 05:30 PM

Do the application.displayalerts=false before you save your file, then after you save it, do application.displayalerts=true.

robfo0 · May 2nd, 2002, 05:39 PM

well thats the problem, the save IS working, i can see it save, but then it asks again on the before close event. and i cant put the displayalerts=false in there because then there wouldnt be a way to turn it back on...

robfo0 · May 2nd, 2002, 05:47 PM

another update: I tried playing around with the code just to make it work. I tried putting "application.displayalerts=false" in the workbook_open, and every module that runs, and even in the before close event, but the message asking if i want to save STILL comes up!!?! make it stop! does deleting code and sheets require the save question to come up?? if the user clicks no, it causes fatal errors! Help!

robfo0 · May 2nd, 2002, 07:07 PM

Well, after hours of toiling, i finally found the problem. It was in the deleting of the sheets on close. I am now calling form the before_close event the macro "DeleteSheets". I tried deleting both sheets from there, but i was having the same problem. BUT i finally noticed taht if i only delete one sheet per macro, the save message did NOT come up. IT works! But my question now is: does anyone know why it DOES work? whats the difference of having the delete sheets in 2 macros as opposed to one?

Thanks

Sub DeleteSheets()
Application.DisplayAlerts = False
Sheets("Navigation").Delete
Application.DisplayAlerts = True
Call DeleteDataSheet
End Sub

Sub DeleteDataSheet()
Application.DisplayAlerts = False
Sheets("DataSheet").Delete
Application.DisplayAlerts = True
End Sub

brettvba · May 2nd, 2002, 09:01 PM

you could put this in the workbook before close event

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
If ThisWorkbook.Saved = True Then Cancel = False

End Sub

and the macro
Sub DeleteSheets()
Application.DisplayAlerts = False
Sheets("Navigation").Delete
Sheets("DataSheet").Delete
Application.DisplayAlerts = True
activeworkbook.save
Workbooks("Book1").Close
End Sub

Qroozn · May 2nd, 2002, 09:03 PM

were you putting the displayalerts=true before the second file was deleted?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 05:28 PM	#1
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	OK, a problem: I have a macro which goes through my workbook, and deletes some sheets, and deletes a module in my workbook. This all works fine. But, in my before close event, i have a piece of code which saves the workbook, thus preventing the Save Question box from coming up (well, supposed to). The problem is, when i run my code which deletes the module and sheets, and then close the workbook, the save question STILL comes up! (if i do NOT run the code which deletes, there is NO save message). The big problem here is that IF the user for some reason clicks "no" to the save question, it corrupts the excel file, and i can no longer open it! I do not want to use the code "application.displayalerts=false" because there may be other workbooks open which i DO need alerts. Can anyone think of any solutions? I would post my code, but theres a ton of it. let me know if you really need it, thanks.

May 2nd, 2002, 05:30 PM	#2
zacemmel Board Regular Join Date: Apr 2002 Location: Redmond, WA Posts: 636	Do the application.displayalerts=false before you save your file, then after you save it, do application.displayalerts=true.

May 2nd, 2002, 05:39 PM	#3
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	well thats the problem, the save IS working, i can see it save, but then it asks again on the before close event. and i cant put the displayalerts=false in there because then there wouldnt be a way to turn it back on...

May 2nd, 2002, 05:47 PM	#4
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	another update: I tried playing around with the code just to make it work. I tried putting "application.displayalerts=false" in the workbook_open, and every module that runs, and even in the before close event, but the message asking if i want to save STILL comes up!!?! make it stop! does deleting code and sheets require the save question to come up?? if the user clicks no, it causes fatal errors! Help!

May 2nd, 2002, 07:07 PM	#5
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	Well, after hours of toiling, i finally found the problem. It was in the deleting of the sheets on close. I am now calling form the before_close event the macro "DeleteSheets". I tried deleting both sheets from there, but i was having the same problem. BUT i finally noticed taht if i only delete one sheet per macro, the save message did NOT come up. IT works! But my question now is: does anyone know why it DOES work? whats the difference of having the delete sheets in 2 macros as opposed to one? Thanks Sub DeleteSheets() Application.DisplayAlerts = False Sheets("Navigation").Delete Application.DisplayAlerts = True Call DeleteDataSheet End Sub Sub DeleteDataSheet() Application.DisplayAlerts = False Sheets("DataSheet").Delete Application.DisplayAlerts = True End Sub

May 2nd, 2002, 09:01 PM	#6
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	you could put this in the workbook before close event Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayAlerts = False If ThisWorkbook.Saved = True Then Cancel = False End Sub and the macro Sub DeleteSheets() Application.DisplayAlerts = False Sheets("Navigation").Delete Sheets("DataSheet").Delete Application.DisplayAlerts = True activeworkbook.save Workbooks("Book1").Close End Sub

May 2nd, 2002, 09:03 PM	#7
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	were you putting the displayalerts=true before the second file was deleted?