Protected Sheet Warning On Any Edit

Question

Hoping to get some help with Excel 2010.

I have three visible sheets. On Workbook Open, vba password protects each of the three sheets.

Now, whenever I make a change to any unlocked cell in any sheet, I get 4 protected sheet warning pop-ups. The one that says that the cells are locked and you have to unprotect to edit? That's all well and good except that I am not editing locked cells. I am editing unlocked cells in other sheets!

Has anyone had this experience? I have played around and two of them can be attributed to each of two sheets. That is to say, when I only protect the first sheet, I get no pop-ups, when I protect only the second, I get 2 and when I protect only the third, I get 2.

Taking out formulas hasn't made a difference.

Here is the code for the locking:

For Each wSheet In Worksheets

    wSheet.Unprotect Password:="JMP_DST_Lock"

    If wSheet.Visible = True Then
        wSheet.Protect Password:="JMP_DST_Lock", UserInterFaceOnly:=True
        wSheet.EnableOutlining = True
    End If

Next wSheet

ThisWorkbook.Protect Password:="JMP_DST_Lock"

Thank you very much for any help.

EDIT: Turns out the comboboxes are the reason for the error. If the linked cells are locked, anytime the sheet calculates (any change when on automatic calculation) causes the warnings. Now, I have code that unprotects the sheets on each combobox GotFocus, but aside from that, these are cropping up.

Is there a middle ground? A way to keep the linked cells locked without these warnings popping up? A way to make sure the comboboxes are hitting the linked cells except on selection?

Thank you!

Ken, sorry about the shouting. Frustration coming through the keyboard. You're right, shouldn't have done it. Thank you for removing. I am adding the code to the top to show you the locking. — JMP
Not sure if your EDIT is providing more information about the first question, or if you're asking another question. If it's the latter, you should start a new (separate) post to ask it. — Ken White

Ken White Ken White · Accepted Answer · 2012-09-28T22:21:01

Your logic seems wrong. You're unprotecting the sheet, then protecting visible sheets, and then protecting the entire workbook (in the last code line).

Try something like this instead (untested):

For Each wSheet In Worksheets
  If wSheet.Visible = True Then
    wSheet.Protect Password:="JMP_DST_Lock", UserInterFaceOnly:=True
    wSheet.EnableOutlining = True
  End If
Next wSheet

If the workbook is saved with the sheets in protected state, change it to something like this instead:

For Each wSheet In Worksheets
  If wSheet.Visible = False Then
    wSheet.UnProtect Password:="JMP_DST_Lock"
  End If
Next wSheet

Protected Sheet Warning On Any Edit

1 Answers