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-03T22:04:33+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!

Hi,

That's easy you can do this. Note that the password is case sensitive

Sub somesub()

ActiveSheet.Unprotect Password:="secret"

    'do things with your code

ActiveSheet.Protect Password:="secret"

End Sub

Was this answer helpful?

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

10 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-02-03T20:07:24+00:00

    THANK YOU!!!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-02-03T19:12:51+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?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-02-03T18:34:32+00:00

    Hi,

    Looking at the first question and the code below, It can never work in its current form. The reason for that is the first time it executes; assuming the sheet wasn't manually protected before that first execution, it will work fine, it will:-

    1. Un-Protect the sheet, it doesn't matter that it's already unprotected.
    2. Execute your code
    3. protect the sheet

    However now we have a problem because the sheet is protected and cannot be changed and therefore the code cannot be called again unless you manually intervene an un-protect the sheet using

    Home tab|Format|Unprotect sheet

    With regard to the ssecond question, this line does nothing useful

    Range("Q11").Activate

    but apart from that the code should work fine.

    Was this answer helpful?

    0 comments