Excel – Close the workbook with or without saving

[wpfilebase tag=file path='01/SaveNoSave.zip']

The entry is a simple example of how to close a workbook and either force a save, or close without saving.

These are one line entries intended to be used with a command button, menu bar button or in another procedure.

There are always instances where you will want to force your users to save a workbook upon exiting, or exit without saving anything. These lines of code are intended to demonstrate how to do this, and would most likely be assigned to a command button, menu bar button, or entered into another procedure.

NOTE: The CloseNoSave will not prohibit a user from saving the workbook through other means.

Sub CloseNoSave()

'Close the workbook without saving it
 ThisWorkbook.Close savechanges:=False

End Sub
Sub CloseForceSave()

'Save the workbook, then close it
 ThisWorkbook.Close savechanges:=True

End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.

Test the code:

  1. Enter some data on the worksheet.
  2. Press Alt + F8 to bring up the macro dialog box.
  3. Choose a macro to run.
  4. When the workbook closes, reopen it.
  5. If you chose CloseNoSave, the changes you made in step 1 will not have been saved. If you chose CloseForceSave, the changes you made in step 1 will have been saved.

LINK (Vbaexpress.com)