Don't save when user closes workbook

gustoaf

New Member
Joined
Sep 23, 2008
Messages
4
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Right click the excel icon to the left of the File menu, View Code, and paste this.
Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Similar to Kenneth:

Code:
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

Code:
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

Code:
Application.EnableEvents = True
The file now should not be savable and shouldn't give any warning when closed.
 

angusl

Board Regular
Joined
Apr 15, 2002
Messages
55
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

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

Forum statistics

Threads
1,082,551
Messages
5,366,276
Members
400,881
Latest member
DevelopedUnkown

Some videos you may like

This Week's Hot Topics

Top