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
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
methods to work together with the Range object’s
Locked property to conditionally protect cells on a worksheet. For instance, the following code protects all worksheet ...