Programming Excel with VBA and .NET

Program with Protection

Since protecting workbooks, worksheets, and ranges is a multistep process, it is sometimes 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 26-13 illustrates the relationships among the protection objects.

Figure 26-13. 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 since those objects allow only 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.

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 all worksheet cells that contain formulas:

 Set ws = ThisWorkbook.Sheets("Protection") ' Make sure worksheet is not already protected. ws.Unprotect ' Get each used cell in the worksheet. For Each ...