Monday, August 29, 2005

Protecting Cells in Excel


Microsoft Excel has the ability to "protect" cells from accidental entry, editing or erasure. By default all cells in an Excel spreadsheet are locked. This option can be found by selecting a cell then clicking Format | Cells | Protection.
This has absolutely no affect unless the sheet is protected. You can protect a sheet by clicking Tools | Protection | Protect Sheet and then completing the dialog boxes to add a password to the sheet. Unlocking is done in reverse. Once a sheet is protected cells that were locked are no longer able to be edited or deleted. Cells that had the locked option removed can be.

Excel XP (2002) and Excel 2003 added additional features to the locking process. Some of these options include the ability to restrict users to specific ranges or types of changes to a cell, eg format changes but not formula changes. While these features are quite powerful I don't tend to use them either in spreadsheets or VBA code. The reason is that these are NOT completely backward compatabile. Syntax in VBA that relates to the new features will cause Debug errors when run in older versions of Excel. Some protection methods that reserve some functions do not perform as required under older versions.
When writing spreadsheet solutions for corporate environments where mutliple versions of Excel may exist, including workers home PC's remember not to use these options or alternatively error trap all possible issues with versions in your code.

There were numerous changes between Excel 2000 and Excel 2002 and then 2003 that weere not documented in the whitepapers produced by Microsoft. I have spent many hours on the phone to Microsoft Engineers trying to resolve issues relating to changes that were not documented either in help files or whitepapers. The good news was that these calls were free; the bad news is that the issues have not been resolved. I will discuss some more technical issues later.


Posted by: Jethro | 11:32 PM |