Got any Excel Questions? Free Excel Help
Stop/Prevent Save Prompts in Excel.
There are times when you m ay wish to prevent any "Save" prompts when closing an Excel Workbook manually, or via Excel VBA code. This can be achieved in a number of ways depending on your desired outcome.
Stop/Prevent Save Prompts in Excel & Don't Save, Or Save
If your desired outcome is to NOT save changes, use the first of the codes below. If your desired outcome is to save changes, use the second of the codes below.
Private Sub Workbook_BeforeClose(Cancel As Boolean) Me.Saved = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Me.Save End Sub
One of these codes MUST reside in the Private Module of the Workbook Object (ThisWorkbook). To get there from Excel, left click on the Excel icon, top left next to File and choose "View Code". Paste the code in here. The first simply tricks Excel into thinking the Workbook has been saved. The second forces Excel to always save automatically before closing. You cannot use both Procedures together.
Sub No_Save() ThisWorkbook.Saved = True ''or 'ActiveWorkbook.Saved = True End Sub Sub No_Save() ThisWorkbook.Save ''or 'ActiveWorkbook.Save End Sub
The above code is the same as the Workbook_BeforeClose Procedures shown above. The only difference is, they must reside in a standard module (Insert>Module).
Sub Close_No_Save() ThisWorkbook.Close SaveChanges:=False ''or 'ActiveWorkbook.Close SaveChanges:=False End Sub
The Close_No_Save Procedure above will close Excel and not save any changes made. To force a save change False to True.
|Prevent Save As in Excel|
|Prevent Excel Being Saved With Another Name|
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.