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

Anonymous

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

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

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.

0 comments

11 additional answers

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