Sheet Protection Bug

[bltseattle]

I have a workbook I have been building for a long time. One of the sheets is for user input, and I have all cells locked except those that receive the user input. The protection setting boxes to allow the user to select locked cells and to select unlocked cells are checked.

Today, for no clear reason, I find that when the sheet protection is turned on, I cannot select any cells at all with the left click. The cursor looks like a hand. I can select cells with a right click only. It is as if there is a giant invisible text box over the whole sheet. The other sheets in the workbook still work correctly, e.g. users can select any cell but only edit unlocked cells.

My guess is that I accidently hit some keystroke thing when it was autosaving, althought I really don't know what broke it.

Has anyone else had this problem? Ideas on how to fix it? It is hard to replace this sheet because it has many named cells on it that are referenced on the other 20 sheets in the workbook.

 

[bltseattle]

I have worked around the problem by cutting/pasting cells from the old to a new worksheet. (which suggests is was some sheet-level option) I had hoped for a simpler fix such as a setting or option to change but it only took about 15 minutes to work around.

 

[mgp]

Could it have something do with you having switched version of EXCEL.

When I turn on sheet protection (version 2002) I get a dialog box (with tick marks) asking what I want to protect, including protecting cells from being selected. I don't recall having seen this on earlier versions so maybe the transfer from an earlier version to a later one or vice versa may include cell selection in the protection.

Below is a print of the help topic on worksheet protection.


***************************************************
Elements you can protect in worksheets and workbooks
Worksheets and chart sheets

When you protect a chart sheet or worksheet, you can protect or unprotect individual elements of the sheet in the Protect Sheet dialog box (Protection menu, Tools command) by selecting or clearing check boxes for each element.

Worksheet elements

Protect worksheet and contents of locked cells When selected, prevents users from:

Making changes to cells that you did not unlock before protecting the worksheet, unless you've granted a specific user permission to edit to the cells in the Allow Users to Edit Ranges dialog box.

Viewing rows or columns that you hid before you protected the worksheet.

Viewing the formulas for the cells for which you hid the formulas before you protected the worksheet.

Select locked cells When cleared, prevents users from moving the pointer to cells for which the Locked check box is selected on the Protection tab of the Format Cells dialog box.

Select unlocked cells When cleared, prevents users from moving the pointer to cells for which the Locked check box is cleared on the Protection tab of the Format Cells dialog box. When users are allowed to select unlocked cells, they can press the TAB key to move between the unlocked cells on a protected worksheet.

Format cells When cleared, prevents users from changing any of the options in the Format Cells or Conditional Formatting dialog boxes. If you applied conditional formats before you protected the worksheet, the formatting continues to change when a user enters a value that satisfies a different condition.

Format columns When cleared, prevents users from using any of the commands on the Column submenu of the Format menu, including changing column width or hiding columns.

Format rows When cleared, prevents users from using any of the commands on the Row submenu of the Format menu, including changing row height or hiding rows.

Insert columns When cleared, prevents users from inserting columns.

Insert rows When cleared, prevents users from inserting rows.

Insert hyperlinks When cleared, prevents users from inserting new hyperlinks, even in unlocked cells.

Delete columns When cleared, prevents users from deleting columns. Note that if Delete columns is protected and Insert columns is not also protected, a user can insert columns that he or she cannot delete.

Delete rows When cleared, prevents users from deleting rows. Note that if delete rows is protected and insert rows is not also protected, a user can insert rows that he or she cannot delete.

Sort When cleared, prevents users from using any of the Sort commands on the Data menu, or the Sort buttons on the Standard toolbar. Users can't sort ranges containing locked cells on a protected worksheet, regardless of this setting.

Use AutoFilter When cleared, prevents users from using the drop-down arrows to change the filter on an AutoFiltered range. Users cannot create or remove AutoFiltered ranges on a protected worksheet, regardless of this setting.

Use PivotTable reports When cleared, prevents users from formatting, changing the layout, refreshing, or otherwise modifying PivotTable reports, or creating new reports.

Edit objects When cleared, prevents users from:

Making changes to graphic objects — including maps, embedded charts, shapes, text boxes, and controls — that you did not unlock before you protected the worksheet. For example, if a worksheet has a button that runs a macro, you can click the button to run the macro, but you cannot delete the button.

Making any changes, such as formatting, to an embedded chart. The chart continues to update when you change its source data.

Adding or editing comments.

Edit scenarios When cleared, prevents users from viewing scenarios that you have hidden, making changes to scenarios that you have prevented changes to, and deleting these scenarios. Users can edit the values in the changing cells, if the cells are not protected, and add new scenarios.

Note If you run a macro that includes an operation that's protected on the worksheet, a message appears and the macro stops running.
**********************************************************

This was only a part of it. There's more on charts etc.

Hope this
Regards
Mogens