Section 6.5.Protect Items in a Workbook


6.5. Protect Items in a Workbook

In Excel, protection means preventing changes to parts of a workbook. You can apply protection to worksheets, charts, ranges, formatting, and window layout. Protection can use a password, or it may omit the password if the protection is intended to prevent accidental changes rather than malicious ones.

You can protect multiple items within a workbook and you can use different passwords for each of those items, though that's generally a bad idea. The more passwords you use, the more likely you are to confuse themespecially within a single workbook. It's a good idea to use the same password when protecting multiple items.


Note: Protection allows users to read, but not change, parts of a workbook. Protection is applied in different ways to different items within a workbook.

6.5.1. How to do it

To prevent changes to a worksheet:

  1. Add data to your worksheet and adjust the formatting so it appears the way you want it to.

  2. From the Tools menu, choose Protection then Protect a Sheet. Excel displays the Protect Sheet dialog box shown in Figure 6-8.

  3. Type a password and select the actions you want to permit on the worksheet from the list. Click OK. Excel prompts you to confirm the password.

After a worksheet is protected, you can't change it without unprotecting it first. To unprotect the worksheet, select Tools Protection Unprotect Sheet and enter the password.

Worksheet protection applies to all of the locked cells on a worksheet. To allow users to edit some cells on a worksheet while protecting most of the others, take the following steps before protecting the worksheet:

Figure 6-8. Protecting a worksheet from changes


  1. Select the cells you want to allow the user to edit.

  2. From the Format menu, choose Cells. Excel displays the Format Cells dialog box shown in Figure 6-9.

    Figure 6-9. Unlock the cells to allow edits on protected worksheets


  3. Select the Protection tab and clear the Locked checkbox. Click OK.

  4. Protect the worksheet. Now, Excel allows changes in the unlocked cells.

You can also selectively protect ranges of cells by user. This lets some users but not others edit selected cells. To protect ranges by user, take the following steps before protecting the worksheet:

  1. Select the range of cells to protect.

  2. Choose Tools Protection Allow Users to Edit Ranges. Excel displays the dialog box shown in Figure 6-10.

    Figure 6-10. Adding an edit range to a protected worksheet


  3. Click the New button. Excel displays the New Range dialog box (Figure 6-11) with the range of the selected cells listed in the Refers to cells text box.

    Figure 6-11. Creating the range on which to allow edits


  4. Click the Permissions button then click Add on the Permissions dialog box. Excel displays the Select Users or Groups dialog box (Figure 6-12).

    Figure 6-12. Adding users with permission to edit the range


  5. Type the names of the users to allow to edit the range. Usernames take the form machinename\username for workgroup-based networks or domainname\username for domain-based networks. You can also simply type the username and click Check Names to look up a user's machine or domain name if you don't know it. To specify multiple names, separate them with a semicolon. Click OK when done. Excel adds the names to the Permissions dialog box, as shown in Figure 6-13.

  6. If you want to require the user to enter a password before editing the range, select the username and click the Deny checkbox. Click OK when done. Excel returns you to the New Range dialog box.

  7. Enter a password for the range and click OK. Excel prompts you to confirm the password and then returns you to the worksheet.

  8. Protect the worksheet using the steps at the beginning of this section. Protecting the worksheet activates the protection for the rangeExcel does not enforce those protections until the worksheet is protected.

Figure 6-13. Users with edit permissions


6.5.2. How it works

In general, you use the preceding procedure to allow some users to edit ranges without the worksheet-level password. In this case, you would select the Allow checkbox in Step 6, enter a password in Step 7, and probably specify the same password to protect the worksheet in Step 8. Then, all other users would have to enter a password before making changes to the range or the rest of the worksheet.

If you don't enter a password for the range in Step 7, all users can edit the range. This is equivalent to unlocking the range as described in the previous procedure.

You can allow edits for a group of users. In that case, specify the group name in Step 5. For instance WOMBAT1\Administrators allows members of the Administrators group on the machine Wombat1 to edit a range.

In all cases, you must protect the worksheet in order for the range-level protections to take effect.



    Excel 2003 Programming. A Developer's Notebook
    Excel 2003 Programming: A Developers Notebook (Developers Notebook)
    ISBN: 0596007671
    EAN: 2147483647
    Year: 2004
    Pages: 133
    Authors: Jeff Webb

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net