How to ensure workbook is saved before exiting Excel?

  • Thread starter Thread starter Kathy Love
  • Start date Start date
K

Kathy Love

Need to find "fool-proof" method of ensuring Users save Excel workbooks
before exiting program. This has been a huge problem in the past. Can this
be done with a macro?
 
Kathy
This macro, placed in the ThisWorkbook module, will fire whenever the
user issues a close command (whenever the user clicks on "Close"). The macro
checks if the workbook has been saved, and will save it if not. The
workbook will then close. Come back if you need more. Otto
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
ThisWorkbook.Saved = True
End If
End Sub
 
Every time I come up with something foolproof an even bigger fool comes
along. Can a macro force a save at the end... yes. But that is good and bad.
There are times in the world where you have mucked things up so badly that
you want to exit without saving. If we force the save then your users will
complain in that circumsatance. Additionally if they do not enable macros or
have security set to high then the macros do nothing.

That being said here is the macro...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook
If .Saved = False Then .Save
End With
End Sub

Right click the XL icon beside File in the upper left corner of the scrren
and select view code. Paste the aboce code into the code window.
 
I wouldn't do this.

You may find that a user deleted way too much (on purpose or by accident) and
wants to close the file without saving.

If you make the save automatic, then you'll have a different problem--maybe
worse (depending on the changes and the state of your backups).
 
Kathy said:
Need to find "fool-proof" method of ensuring Users save Excel workbooks
before exiting program. This has been a huge problem in the past. Can this
be done with a macro?
Click to expand...
I have to agree with the other posters who warn about this. I seldom
want to exit Excel without saving, but when I do I really want to revert
to the previous saved version of the spreadsheet. Perhaps adding a
message box to the macro, one of those ridiculous "Do you really want to
save the file before quitting?" may be appropriate, but that will most
likely antagonize your users.

Bill
 
The other responders bring up some good points. You know your situation and
your users better than we do. Unless you have a malicious user who
intentionally wants to not save a changed file, perhaps a message box
telling him that the file has not been saved and asking him if he wants to
save it before closing, would be more appropriate. Your call. If yes, here
is the same macro with the message box. HTH Otto
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = False Then
If MsgBox("This workbook has not been saved." & Chr(13) & _
"Do you want to save it before closing?", 20, "Workbook not
saved") = vbYes Then
ThisWorkbook.Save
ThisWorkbook.Saved = True
End If
End If
End Sub
 
Back
Top