Stop the save message!

robfo0

Active Member
Joined
Feb 19, 2002
Messages
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Do the application.displayalerts=false before you save your file, then after you save it, do application.displayalerts=true.
 
Upvote 0
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...
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top