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:
http://office.microsoft.com/en-us/excel-help/features-that-are-unavailable-in-shared-workbooks-HP005201080.aspx
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When you protect a worksheet, one of the benefits is that you can limit which cells can be used for data entry. How a ...
Discover MoreDo you want user-entered data to be immediately protected so that it cannot be changed? This can be done relatively ...
Discover MoreWhen you are developing a worksheet for others to use, you might want to protect some of the information in that ...
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 © 2023 Sharon Parq Associates, Inc.
Comments