Protection Options | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Excel offers a number of different protection options. For example, you can protect formulas from being overwritten or modified, protect a workbook's structure, password-protect a workbook, and protect your VBA code.

Protecting formulas from being overwritten

In many cases, you might want to protect your formulas from being overwritten or modified. To do so, perform the following steps:

Select the cells that may be overwritten.
Right-click and choose Format Cells from the shortcut menu.
In the Format Cells dialog box, click the Protection tab.
In the Protection tab, clear the Locked check box.
Click OK to close the Format Cells dialog box.
Select Review Changes Protect Sheet to display the Protect Sheet dialog box, as shown in Figure 2-18.

Figure 2-18: The Protect Sheet dialog box.
In the Protect Sheet dialog box, select the options that correspond to the actions to allow, specify a password if desired, and then click OK.

Note	By default, all cells are locked. The locked status of a cell has no effect, however, unless you have a protected worksheet.

You can also hide your formulas so they won't appear in Excel's formula bar when the cell is activated. To do so, select the formula cells and make sure that the Hidden check box is marked in the Protection tab of the Format Cells dialog box.

Protecting a workbook's structure

When you protect a workbook's structure, you can't add or delete sheets. Choose the Review Changes Protect Workbook command to display the Protect Workbook dialog box, as shown in Figure 2-19. Make sure that you enable the Structure check box. If you also mark the Windows check box, the window can't be moved or resized.

Figure 2-19: The Protect Workbook dialog box.

Applying password protection to a workbook

In some cases, you might want to limit access to a workbook to only those who know the password.

To save a workbook file with a password, choose Office Prepare Encrypt Document.. Then, in the Encrypt Document dialog box (see Figure 2-20), specify a password and click OK. Then save your workbook.

Figure 2-20: Use the Encrypt Document dialog box to save a workbook with a password.

Protecting VBA code with a password

If your workbook contains VBA code, you may wish to use a password to prevent others from viewing or modifying your macros. To apply a password to the VBA code in a workbook, activate the VBE (Alt+F11) and select your project in the Projects window. Then choose Tools xxxx Properties (where xxxx corresponds to your Project name ). This displays the Project Properties dialog box.

In the Project Properties dialog box, click the Protection tab (see Figure 2-21). Enable the Lock Project for Viewing check box and enter a password (twice). Click OK and then save your file. When the file is closed and then reopened, a password will be required to view or modify the code.

Figure 2-21: Protecting a VBA project with the Project Properties dialog box.

Caution

It's important to keep in mind that Excel is not really a secure application. The protection features, even when used with a password, are intended to prevent casual users from accessing various components of your workbook. Anyone who really wants to defeat your protection can probably do so by using readily available password-cracking utilities (or by knowing a few "secrets").