Software

Automatically save Excel data, or not, when exiting a workbook

Eliminate the save prompt when exiting an Excel workbook.

Excel offers users one last chance to save changes when exiting a workbook. But occasionally, user interaction isn't necessary, and you can eliminate this step by forcing Excel to do one of two things:
  • Save changes without prompting the user.
  • Don't save changes and don't prompt the user.
There's no built-in setting for altering this behavior. You'll use one of two subprocedures. When you want Excel to save changes to the workbook without prompting the user, add the following subprocedure to the ThisWorkbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
  'Save changes to workbook when closing, without prompting user.
  Me.Save
End Sub
This procedure assumes you will always want to save changes. To exit a workbook without saving changes or prompting the user, add the following subprocedure to the ThisWorkbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
  'Close workbook without saving changes.
  Me.Saved = True
End Sub
This procedure simply tricks Excel. By setting the Saved property to True, Excel believes changes have been saved. You can include both in the same workbook, but you must comment out one of them, as they both use the workbook's Before Close event procedure. In addition, they work only when added to ThisWorkbook.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox