Chapter 31: Protecting Your Work

Assigning user permissions

Excel also offers the ability to assign user-level permissions to different areas on a protected worksheet. You can specify which users can edit a particular range while the worksheet is protected. As an option, you can require a password to make changes.

This feature is rarely used, and the setup procedure is rather complicated. But if you need this level of protection, setting it up might be worth the effort.

1.Unprotect the worksheet if it’s protected.

2.Choose Review Changes Allow Users to Edit Ranges. The dialog box shown in Figure 31.4 opens.

3.Follow the prompts in the series of dialog boxes that follow.

4.Protect the sheet.

FIGURE 31.4

The Allow Users to Edit Ranges dialog box.

Protecting a Workbook

Excel provides three ways to protect a workbook:

•Require a password to open the workbook.

•Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets.

•Prevent users from changing the size or position of windows.

I discuss each of these methods in the sections that follow.

Requiring a password to open a workbook

Excel lets you save a workbook with a password. After doing so, whoever tries to open the workbook must enter the password.

641

Part IV: Using Advanced Excel Features

To add a password to a workbook, follow these steps:

1.Choose File Info Protect Workbook Encrypt With Password. Excel displays the Encrypt Document dialog box shown in Figure 31.5.

2.Type a password and click OK.

3.Type the password again and click OK.

4.Save the workbook.

FIGURE 31.5

Specify a workbook password in the Encrypt Document dialog box.

Note

You need to perform these steps only one time. You don’t need to specify the password every time you resave the workbook. n

To remove a password from a workbook, repeat the same procedure. In Step 2, however, delete the existing password symbols from the Encrypt Document dialog box, click OK, and save your workbook.

Figure 31.6 shows the Password dialog box that appears when you try to open a file saved with a password.

FIGURE 31.6

Opening this workbook requires a password.

642

Chapter 31: Protecting Your Work

Excel provides another way to add a password to a document:

1.Choose Office Save As.

2.In the Save As dialog box, click the Tools button and choose General Options. Excel displays the General Options dialog box.

3.In the General Options dialog box, enter a password in the Password to Open field.

4.Click OK. You’re asked to re-enter the password before you return to the Save As dialog box.

5.In the Save As dialog box, make sure that the filename, location, and type are correct; then click Save.

Note

The General Options dialog box has another password field: Password to Modify. If you specify a password for this field, the file opens in read-only mode (it can’t be saved under the same name) unless the user knows the password. If you use the Read-Only Recommended check box without a password, Excel suggests that the file be opened in read-only mode, but the user can override this suggestion. n

Protecting a workbook’s structure

To prevent others (or yourself) from performing certain actions in a workbook, you can protect the workbook’s structure. When a workbook’s structure is protected, the user may not

•Add a sheet.

•Delete a sheet.

•Hide a sheet.

•Unhide a sheet.

•Rename a sheet.

•Move a sheet.

To protect a worksheet’s structure

1.Choose Review Changes Protect Workbook to display the Protect Workbook dialog box (see Figure 31.7).

2.In the Protect Workbook dialog box, select the Structure check box.

3.(Optional) Enter a password.

4.Click OK.

To unprotect the workbook’s structure, choose Review Changes Unprotect Workbook. If the workbook’s structure was protected with a password, you are prompted to enter the password.

643

Part IV: Using Advanced Excel Features

FIGURE 31.7

The Protect Workbook dialog box.

Protecting a workbook’s windows

To prevent others (or yourself) from changing the size or position of a workbook’s windows, you can protect the workbook’s windows:

1.Choose Review Changes Protect Workbook.

2.In the Protect Workbook dialog box, select the Windows check box.

3.(Optional) Enter a password.

4.Click OK.

When a workbook’s windows are protected, the user can’t change anything related to the window size or position. For example, if the workbook window is maximized when the windows are protected, the user cannot unmaximize the window. The windows can, however, be zoomed.

To unprotect the workbook’s windows, choose Review Changes Unprotect Workbook. If the workbook’s windows were protected with a password, you are prompted to enter the password.

VB Project Protection

If your workbook contains any VBA macros, you may want to protect the VB Project to prevent others from viewing or modifying your macros. Another reason to protect a VB Project is to prevent its components from being expanded in the VB Editor Project window (which can avoid clutter while you’re working on other VB project). To protect a VB Project

1.Press Alt+F11 to activate the VB Editor.

2.Select your project in the Projects window.

3.Choose Tools - xxxx Properties (where xxxx corresponds to your Project name).

Excel displays the Project Properties dialog box.

4.In the Project Properties dialog box, click the Protection tab (see Figure 31.8).

5.Select the Lock Project for Viewing check box.

644