Written by Allen Wyatt (last updated December 21, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Gary has a read-only workbook that multiple users can access. They can modify cells but not save their work. On exiting the workbook, Gary wants Excel to just close without informing the user that it is read-only and giving them the option of saving a copy.
This is best accomplished by using a macro to modify the Saved flag in the workbook, just before closing. This flag indicates, internally, whether a workbook needs saving or not. If the flag is False, then Excel knows that the workbook has not been saved (changes have been made without saving). If your macro sets the flag to True, then Excel will close directly because it thinks that all the changes have been saved.
Here's what the macro should look like, at its simplest:
Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.ReadOnly Then ThisWorkbook.Saved = True End If End Sub
The macro should be added to the ThisWorkbook object in the VBA Editor. That way, it is automatically executed just before the workbook is closed. The flag is set to True, and when the macro ends, Excel continues with its normal closing procedures. Since Excel thinks that there are no unsaved changes, the user sees no message and the workbook is closed.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10349) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Ever want to see a list of all the workbooks that are open? If you open more than nine, Excel only displays the first ...
Discover MoreIf you need to combine the contents of a bunch of workbooks into a single workbook, the process can get tedious. Here's a ...
Discover MoreDoes your macro need to make sure that the workbook being processed is saved to disk? You can add the saving capability ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments