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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2013-02-03T21:15:47+00:00

    What you have should work. As far as I can tell and unless I am missing something, the statement by Mike H. is not correct. If you unprotect the worksheet each time the code is entered and protect as the code exits, you should be able to do anything you want between the unprotect and protect statements. And the code can be invoked repeatedly and will function the same each time.

    Hi,

    The point I was trying to make is that the code below doesn't work more than once unless you manually unprotect the sheet.. You can't call worksheet_change code if you can't change the worksheet.

    In making that statement and because there was no reference to it in the question I assume the OP hasn't explored and is not using un-locked cells on this sheet which of course can be changed while protected..

    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

    Was this answer helpful?

    0 comments
  2. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2013-02-03T21:43:51+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

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-02-03T21:58:31+00:00

    Ok so your code worked great.. but I have a password protected sheet.. so is it possible to make the code to unlock the worksheet with the password and then lock it back with the password.

    That would be great if it's possible.. would really appreciate it.. thank you!

    Was this answer helpful?

    0 comments
  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