Excel 2003 Programming: A Developer's Notebook

Program with Protection

Since protecting workbooks, worksheets, and ranges is a multi-step process, it is sometime convenient to automate protection—particularly if you frequently use the same types of protections or if you want to make sure all protections use the same password.

Excel provides methods for protecting Workbook, Chart, and Worksheet objects as well as subordinate objects for controlling various aspects of protection on Worksheet objects. Figure 6-14 illustrates the relationships between the protection objects.

Figure 6-14. Protection object model

The protection objects are organized in a somewhat unusual way: First, the Workbook and Chart objects don’t provide a Protection object, because those objects only allow password-protection. Second, the Worksheet object provides a Protection object that allows you to specify a list of users who can edit ranges on the worksheet. Finally, you set which cells on a worksheet are protected by setting the Range object’s Locked property.

Note

The protection objects aren’t as consistent as other parts of the Excel object model. You need to know where to look to find the right object.

How to do it

You can use the Worksheet object’s Protect and Unprotect methods to work together with the Range object’s Locked property to conditionally protect cells on a worksheet. For instance, the following code protects ...