Microsoft KB Archive/289269

= Description of the worksheet-protection options in Excel 2002 =

PSS ID Number: 289269

Article Last Modified on 9/8/2004

-

The information in this article applies to:


 * Microsoft Excel 2002

-



This article was previously published under Q289269





For a Microsoft Office Excel 2003 version of this article, see 826923.



SUMMARY
This article describes the enhanced worksheet-protection options available in Microsoft Excel 2002.



Menus and Toolbars
Excel 2002 contains a new menu command that provides enhanced worksheet protection. Under Protection on the Tools menu, there is a new command: Allow Users to Edit Ranges.

In the Allow Users to Edit Ranges dialog box, you can list named ranges and their corresponding cells for which specific users, computers, or groups have been designated for special access by using an assigned password. Through this dialog box, you can also create new ranges, modify existing ranges, or delete ranges for which permission has been given for editing.

NOTE: The Allow Users to Edit Ranges command is not available if the worksheet is protected.

Excel 2002 has a new toolbar that contains all of the commands available on the Protection submenu as well as the Lock Cell command. You can make the Protection toolbar appear by clicking to select the Protection check box under Toolbars on the View menu.

Adding User or Group Permissions
If your workstations are running Microsoft Windows 2000, Microsoft Windows NT, or Microsoft Windows XP, Excel workbooks can use edit permissions based on users or groups instead of needing passwords to restrict access to workbooks.

You can add user or group permissions only on Windows 2000 and Windows XP because Excel uses the Select User, Computers, or Groups dialog box, which is not available to programs on other operating systems (Microsoft Windows NT, Microsoft Windows 98, or Microsoft Windows Millennium Edition [Me]).

On a Windows 2000 or Windows XP workstation, the Allow Users to Edit Ranges dialog box is enlarged to include the Specify Who May Edit the Range Without a Password section, which contains a Permissions button. The Permissions button is also available in the New Range and Modify Range dialog boxes.

When you click the Permissions button, the Permissions for Range  dialog box appears.

The Permissions for Range  dialog box contains the following elements:
 * The Group or user names list (Windows XP) or the Name list (Windows 2000), which contains the users and groups that have been added, to which you can allow or deny permissions.
 * The Add button, which displays the Select Users, Computers, or Groups dialog box. You can then select the users and groups that you want to give permissions to and add them to the Name list in the Permissions for Range  dialog box.

NOTE: For more information about users and groups, or the Select User, Computers, or Groups dialog box, see the Windows 2000 or Windows XP Help files.
 * The Remove button, which removes the selected item in the list of names. There is no confirmation to undo the action. Instead, you must click Cancel in the Permissions for Range Name dialog box.
 * The Permissions options box, which contains check boxes to either allow or deny the selected user or group permission to edit the range without a password.

When you click to select the Allow check box, the specified user can edit the range without using a password if that user is logged on to the domain validated by Windows 2000, Windows NT, or Windows XP.

When you click to select the Deny check box, the specified user must type the correct password to edit the range. When the Deny check box is selected, a message appears when you click OK or Apply to report that Deny permissions take priority over Allow permissions, and the message gives the user the option to continue or not. If your user has overlapping permissions of both Deny and Allow, the user is required to type a password to edit the range.

NOTE: On Windows 98 or Windows Me workstations, or when your user cannot be validated, the correct password is required to edit the range.

IMPORTANT: User permissions are overridden if a password is not specified for the range, and then the range can be edited by anyone.

You must protect the worksheet to make the ranges to edit with a password take effect. If you do not protect the worksheet, anyone may edit the worksheet.

If the cells Locked property is not enabled, this state takes precedence over any ranges specified and, therefore, anyone may edit any unlocked cell.

Enhanced Worksheet-Protection Options
The Protect Sheet dialog box has been updated to include many new protection options.

The new Protect Sheet dialog box allows you to select protection for objects and scenarios, as did earlier versions of Excel. However, in Excel 2002, cell contents are always protected when the worksheet is protected.

TIP: You can use Microsoft Visual Basic for Applications (VBA) to protect a worksheet for objects and scenarios, but not for contents.

Cell contents can be unlocked on a user or password level, but the worksheet-protection options can be changed only with the worksheet-protection password. If a cell is locked in the Format Cells dialog box, it is always considered locked for the worksheet protection. If a user has permission to edit a range that includes a locked cell, the cell is still considered locked because the range permissions override the locked cell format but do not change it. Users with permission to edit a range of cells are still restricted by the worksheet-protection options. For example, if the Format cells check box is cleared, no one can format any cells in the protected worksheet even if they have the password or permissions to edit the cells.

The Protect Sheet dialog box contains the following check boxes:  The Select locked cells check box (selected by default) allows users to select cells with the Locked check box selected in the Format Cells dialog box. When the Select locked cells check box is selected, the Select unlocked cells check box is automatically selected. The Select unlocked cells check box (selected by default) allows users to select cells with the Locked check box cleared in the Format Cells dialog box. When the Select unlocked cells check box is cleared, the Select locked cells check box is automatically cleared. If there are no unlocked cells on a protected sheet and this check box is not selected, users are unable to select any cells on the worksheet. The Format cells check box enables Conditional Formatting and enables every control in the Format Cells dialog box for every cell on the worksheet, including locked cells, with the following exceptions:

 The Protection tab is always hidden on a protected sheet. Merge cells on the Alignment tab is always unavailable.</ul>

NOTE: You cannot permit formatting of unlocked cells only.</li> The Format columns check box enables every item in the Column submenu of the Format menu.</li> The Format rows check box enables every item in the Row submenu of the Format menu.</li> The Insert columns check box lets a user insert columns anywhere on the worksheet (subject to normal conditions).

NOTE: If the Delete columns check box is also cleared, the user can insert columns that cannot be deleted except by using the Undo command.</li> The Insert rows check box lets a user insert rows anywhere on the worksheet (subject to normal conditions).

NOTE: If the Delete rows check box is also cleared, the user can insert rows that cannot be deleted except by using the Undo command.</li> The Insert hyperlinks check box makes the Hyperlink command on the Insert menu available when the current range of selected cells consists of only unlocked cells.</li> The Delete columns check box allows a user to delete any column that does not contain a locked cell. If a user attempts to delete a column that contains a locked cell, a message appears stating that you cannot delete a locked cell in a protected worksheet.</li> The Delete rows check box allows a user to delete any row that does not contain a locked cell. If a user attempts to delete a row that contains a locked cell, a message appears stating that you cannot delete a locked cell in a protected worksheet.</li> The Sort check box enables the Sort dialog box on the Data menu. Sorting is possible only if the range to be sorted does not contain locked cells. If a user attempts to sort a range that contains locked cells, a message appears stating that you must unprotect the sheet before the data can be sorted.</li> The Use AutoFilter check box lets a user change the filter criteria on an existing AutoFilter. This check box does not allow the user to add or remove an AutoFilter. The AutoFilter must exist before the user protects the sheet.</li> The Use PivotTable reports check box allows a user to manipulate an existing PivotTable. This check box does not allow the user to add or remove a PivotTable. The PivotTable must exist before the user protects the sheet.</li> The Edit objects check box removes any protection from an object except any protection properties set for the object.</li> The Edit scenarios check box removes protection from scenarios.</li></ul>

Backward Compatibility
The new protection features are not recognized in versions of Excel earlier than Excel 2002. If you save the file in an Excel 95-or-earlier file format, you lose the new protection options and edit ranges. The legacy protection options are saved correctly in earlier Excel file formats that support the protection options. These options consist of the sheet-protection password, and the Edit Object, Edit Scenario, and Protect Contents items (not available in the user interface of Excel 2002). Protection options and password edit ranges are preserved when a file is saved in the Excel 97 and Excel 2000 file format. The use of future record types allows you to save the file in Excel 97 or Excel 2000 and still maintain the new features when you open the file back into Excel 2002.

When you open an Excel 2002-protected sheet in an earlier version of Excel, the Excel 2002 protection functionality reverts back to the level of protection available in that version of Excel. In general, the new protection features in Excel 2002 are exceptions to the default level of protection. In other words, if you use Excel 2000 to open a workbook created in Excel 2002, you are not allowed to make changes that are protected in Excel 2002. Protection options (for example, enabling Insert columns on a protected sheet) do not apply to earlier versions: Everything that is disabled on a protected worksheet in Excel 2000 and earlier remains protected, even if the workbook was created in Excel 2002.

Password edit ranges have no meaning in earlier versions of Excel. Users and passwords for ranges are not validated, and therefore protection is not compromised. The ranges remain locked. The cells cannot be edited as long as the worksheet is protected and the cells have the Locked format set. If you neglect to perform either of these actions, the cells can be edited in Excel 2002 without a password.

The Excel 95, 97, and 2000 VBA properties work as if they were set by using VBA if the corresponding options are set through the Excel 2002 Protect Sheet dialog box.

When you open a workbook that is protected in an earlier version of Excel, the workbook opens in Excel 2002 with the protection level matching the options set in the earlier version.

Using Enhanced Encryption
In Excel 2002, in addition to the encryption schemes available in earlier versions of Excel, you now have access to the encryption schemes from Internet Explorer's CryptoAPI, available in Internet Explorer 4 and 5.

You can access the Encryption Type dialog box by clicking the Advanced button on the Security tab of the Options dialog box (click Options on the Tools menu). You can also access the Encryption Type dialog box in the Save As dialog box by following these steps:
 * 1) On the File menu, click Save As.
 * 2) In the Save As dialog box, click Tools on the standard toolbar.
 * 3) Click the General Options tab.
 * 4) In the Save Options dialog box, click Advanced.

Use caution when you select the encryption type with which to protect a workbook because it may not be available on all systems. The Office 97 and 2000-compatible encryption type is the default because it is the most likely to be available. If you attempt to open a workbook that is encrypted by an encryption type that is not installed, you may receive a message stating that Excel cannot open the workbook.

Workbooks encrypted with the newer encryption types cannot be opened in earlier versions of Excel. You should always use the Office 97 and 2000-compatible encryption type if you need to open these workbooks in earlier versions of Excel. When you attempt to open a workbook protected with a newer encryption type in an earlier version of Excel, you are prompted to type the password and, even if you type the correct password, Excel cannot recognize the password and alerts you that the password is incorrect.

The workbook can be shared in the same manner as in earlier versions except that you must have Excel 2002 and the appropriate encryption provider installed to open the workbook when you use an enhanced encryption scheme. If you use an enhanced encryption scheme, earlier versions of Excel cannot validate the password, and you are alerted that the password is incorrect. When the workbook is shared, you cannot change the encryption type or password. The corresponding elements in the Security and Save Options dialog boxes are disabled when the workbook is shared.

You cannot share a workbook that has a Weak Encryption (XOR) setting on a system with regional settings other than French (Standard). When you attempt to share the workbook with XOR encryption, you receive an alert that the workbook has been password-protected with an XOR encryption scheme and cannot be shared in this region.

<div class="references_section">