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, and Excel in Office 365.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Want a quick way to access the workbooks you use most often? You can "pin" the workbooks so they can be opened with a click.
Discover MoreWhen you need to work on a workbook, you may want to do so without modifying the original contents of the workbook. This ...
Discover MoreHave you ever tried to save a workbook, only to be notified that someone else has made changes in it? What if you are the ...
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 © 2021 Sharon Parq Associates, Inc.
Comments