by Allen Wyatt
(last updated May 24, 2014)
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When you are developing a worksheet for others to use, you might want to protect some of the information in that ...Discover More
Need to know if a worksheet or workbook is currently protected? Excel provides some tell-tale signs, but here are some ...Discover More
When you first create a worksheet, all the cells in that worksheet are formatted as locked. As you unlock various cells ...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.