Don't save when user closes workbook

[gustoaf]

Is there a line of code that I could throw into my workbook that will not save any changes when the user closes the file?

I have a shell spreadsheet that runs a macro, populates the sheets with external data, and changes the names of the sheets. The data that is populated is for visual reference only.

When the user closes the app, I want the workbook to revert to its "shell" state, with no dialog boxes asking if the user wants to save the file. Just X out and not save.

Thanks.

 

[Kenneth Hobson]

Right click the excel icon to the left of the File menu, View Code, and paste this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  ThisWorkbook.Close False
End Sub

Of course if they save the file and then close it, this alone won't do it for you.

 

[VoG]

Similar to Kenneth:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Close savechanges:=False
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub

Whilst still in the code window press CTRL + G to open the Immediate window, type in

Application.EnableEvents = False

and press Enter. This will prevent the above code from running. Then save the file. Reopen the file, press ALT + F11 to open the VBE, press CTRL + G and type in

Application.EnableEvents = True

The file now should not be savable and shouldn't give any warning when closed.

 

[angusl]

This eliminates the 'Do you want to save changes?' message but Excel itself is still open. Is there a way to make Excel go away so the application shutdown is seamless without dialogs?

 

[VoG]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Close savechanges:=False
Application.Quit
End Sub