Enabling cell range editing by certain users

If you're running Excel 2000 or 2003 on a network or a machine that's being shared with other users, you can enable the editing of individual ranges in the protected worksheet for just certain users. When you use this feature, you give particular users permission to edit particular cell ranges, provided that they can correctly provide the password you assign to that range.

To give access to particular ranges in a protected worksheet, follow these steps:

1. Choose Tools → Protection → Allow Users to Edit Ranges to open the All Users to Edit Ranges dialog box.
   Note that if the worksheet is currently protected, the Allow Users to Edit Ranges menu item is grayed out and unavailable. In that case, you must remove protection with the Tools → Protection → Unprotect Sheet command before you follow Step 1.
2. Click the New button to open the New Range dialog box.
3. Type a descriptive name for the range in the Title text box (or accept the name Excel assigns to the range such as Range1, Range2, and so on).
4. Select the Refers to Cells text box and then type in the address of the cell range (without removing the = sign) or select the range or ranges in the worksheet.
   If you select the range by dragging through its cells, Excel automatically reduces the New Range dialog box to the Refers to Cells list box.
5. Type in the password for accessing the range in the Range Password text box.
6. Click the Permissions button to open the Permissions for Range1 (or whatever you've named the range).
7. Click the Add button to open the Select Users or Groups dialog box.
8. Select the name of the user in the Enter the Object Names to Select list box. To select multiple users from this list, hold down the Ctrl key as you click each user name.
   If this list box is empty, click the Advanced button to expand the dialog box and then click the Find Now button to locate all the users for your location. You can then click the name or Ctrl+ click the names you want to add from this list, and then when you click OK, Excel returns you to the original form of the Select Users or Groups dialog box and adds these names to its Enter the Object Names to Select list box.
9. Click OK in the Select Users or Groups dialog box to close it and return to the original Permissions dialog box.
10. Click the name of the first user who must know the password and then select the Deny check box in the Permissions For list box.
11. Repeat Step 10 for each user who must know the password and then click OK.
    Excel then displays a warning alert dialog box, letting you know that you're setting a deny permission that takes precedence over any allows entries. That mean that if the person is a member of two groups, one with an allow entry and the other with a deny entry, the deny entry permission rules (meaning that the user must know the range password).
12. Click the Yes button in the Security alert dialog box to close it and return to the New Range dialog box.
13. Click OK in the New Range dialog box to close it and open the Confirm Password dialog box.
14. Type the range password in the Reenter Password to Proceed text box and then click OK to close it and return to the Allow Users to Edit Ranges dialog box.
    The Allow Users to Edit Ranges dialog box now contains the name and reference of the new range you just specified as one that can be unlocked when the worksheet is protected.
    If you need to define other ranges available to other users in the worksheet, you can do so by repeating Steps 2 through 14.
    When you finish adding ranges to the Allow Users to Edit Ranges dialog box, you're ready to protect the worksheet. If you want to retain a record of the ranges you've defined, follow Step 15. Otherwise, skip to Step 16.
15. Select the Paste Permissions Information Into a New Workbook check box if you want to create a new workbook that contains all the permissions information.
    When you select this check box, Excel creates a new workbook whose first worksheet lists all the ranges you've assigned, along with the users who may gain access by providing the range password. You can then save this workbook for your records. Note that the range password is not listed on this worksheet; if you want to add it, be sure that you password-protect the workbook so that only you can open it.
    Now, you're ready to protect the worksheet. If you want to do this from within the Allow Users to Edit Ranges dialog box, click the Protect Sheet button to open the Protect Sheet dialog box. If you want to protect the worksheet later on, click OK to close the Allow Users to Edit Ranges dialog box and then choose the Tools → Protection → Protect Sheet whenever you're ready to turn on the worksheet protection.
16. Click the Protect Sheet button to protect the worksheet; otherwise, click OK to close the Allow Users to Edit Ranges dialog box.

If you click the Protect Sheet button, Excel opens the Protect Sheet dialog box where you can set a password to unprotect the sheet as well as to select the actions that you permit all users to perform in the protected worksheet. After you turn on protection in the worksheet, only the users you've designated are able to edit the cell range or ranges you've specified. Don't forget to supply the range password to all the users who are allowed to do editing in the range or ranges at the time you distribute the workbook to them.