Excel

Close the workbook with or without saving

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Ken Puls

Description:

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. 

Discussion:

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 

Code:

instructions for use

			

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 CloseForeSave, the changes you made in step 1 will have been saved.
 

Sample File:

SaveNoSave.zip 6.8KB 

Approved by mdmackillop


This entry has been viewed 424 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express