×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sheet Protection Bug

Sheet Protection Bug

Sheet Protection Bug

(OP)
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.

RE: Sheet Protection Bug

(OP)
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.

RE: Sheet Protection Bug

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



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources