Errors when protected... how to fix?!?

Anonymous
2013-02-03T17:06:56+00:00

Ok so I thought I was done with my workbook and I start to protect everything, for realistic testing. But now in protected mode everything is giving errors  because it says cells needed are protected.. even though their unlocked. So I was wondering if their is something that can unlock those supposely locked cells for a second just while the VBA uses them.

Something like:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim Cell As Range

Sh.Unprotect

' code goes here

...

Sh.Protect

End Sub

But for some reason that doesn't work. So is there something else that I could do to fix this problem.

Also, can some one explain to me how to add them because I tried adding the above to this:

Sub ClearEverything()

'

' ClearEverything Macro

'

'

    Range( _

        "B10:B14,E10:E14,I14:I16,H21:J22,D26,C26,C27:C28,D28:E28,E27,E26,J25:J28,Q24:Q25,Q14,Q5:R11" _

        ).Select

    Range("Q11").Activate

    Selection.ClearContents

    Range("Q5:R5").Select

End Sub

 AND ALSO TO A NORMAL VBA

And had no success. Can someone please show me how to do this, becuase if I get this I can finish my workbook, and this is the only thing holding me back now.

So I would GREATLY appreciate the help... THANK YOU in advance to everyones help!!!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2013-02-03T19:27:24+00:00

No those are all cells that when I click a button are deleted. But I need to protect the worksheet so, could I go about doing that, while still allowing everything to work?

Hi,

If the second piece of code is attached to a button then you can unprotect at run time, clear the cells and re-protect like this.

Sub ClearEverything()

ActiveSheet.Unprotect

     Range( _

         "B10:B14,E10:E14,I14:I16,H21:J22,D26,C26,C27:C28,D28:E28,E27,E26,J25:J28,Q24:Q25,Q14,Q5:R11" _

         ).ClearContents

     Range("Q5:R5").Select

     ActiveSheet.Protect

 End Sub

Going back to the first snippet of code you can't make that work in its current form for the reasons I gave in my last post.

Was this answer helpful?

0 comments

11 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2013-02-03T22:47:28+00:00

    Then then you missed this from the OP's question:

    "But now in protected mode everything is giving errors  because it says cells needed are protected.. even though their unlocked."

    You CAN change a sheet that is protected - you just can't change locked cells. You also can or can't change other sheet properties based on the selections made when the sheet was protected.

    Kevin

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-02-03T22:11:12+00:00

    Are you not aware of the Sh.Unprotect at the beginning of that code segment?

    That unprotects the worksheet and allows anything to be done - to both locked and unlocked cells. The Sh.Protect at the end of the code segment restores protection.

    So, as far as I can tell, the code will produce the same results multiple times and without regard to how worksheet protection is set prior to the first time it is invoked.

    Kevin

    Hi,

    Yes I can see that and am aware of how it works but it will never be called because it's worksheet change code and once the code has run once the sheet becomes protected and you can't change the sheet to make it work a second or subsequent time so sh.unprotect in that context is useless. It doesn't work.

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more