by Allen Wyatt
(last updated February 19, 2019)
Andries has a shared workbook on a network and it works great. It allows multiple teachers at his school to make changes in the various worksheets, within limits he specifies through cell and worksheet protection. The shared workbook also allows him to track changes so he can see what happens to the data in the worksheets. Andries would like a way, though, to automatically "lock" a worksheet after a given date so it can no longer be changed. If he attempts to do it manually, he loses all of the tracked changes, which is unacceptable. He wonders if there is a way to lock a worksheet automatically, by date, and not lose the tracked changes.
There is no way to do this in Excel. The problem is just as you mentioned—when you unshared the workbook (which must be done to make the desired changes to it—you lose the tracked changes. This, for whatever reason, is designed into Excel. In fact, there are a number of features that are made unavailable whenever you share a workbook. The following page at the Microsoft site, even though it says it is for Excel 2003, is also applicable to later versions:
There is one possible way around this if you want to protect the entire workbook: You could simply change the workbook itself so it is read-only. This would have to be done outside of Excel, simply because you cannot do it on a workbook that is currently open. It could, however, be done using a batch file, which means you could set up a Windows scheduled task to run it every day or week (whatever is best for your needs).
If you make the entire workbook file read-only, then people won't be able to change anything within it. That will help to "freeze" the workbook to whatever its condition is on a particular date.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13117) applies to Microsoft Excel 2007, 2010, and 2013.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
If you have a worksheet protected, it may not be immediately evident that it really is protected. This tip explains some ...Discover More
Want to stop a user from moving or copying a worksheet? This task (like many) can be more complex than one would hope. ...Discover More
If you receive a protected worksheet that you want to edit, how do you proceed if you try to unprotect the worksheet and ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.