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. 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