Solved: Protection Issue?

[gimli]

Hey all,

The following code works fine when the sheet is unprotected. As soon as I protect it only the first 2 subs run on a drop down change. Each sub has its own unprotect then protect code so thats not the problem. Any ideas to why that happens?

[VBA]
Private Sub TOLCHOICE_Change()

On Error GoTo Exits
Application.EnableEvents = False

Call test1
Call test2
Call test3


Exits:
Application.EnableEvents = True
End Sub
[/VBA]

[mdmackillop]

Not really. What steps have you tried so far to find the cause?

[gimli]

I rem'd out all the protection code....took data protection off the sheet and everything ran fine. Tested out all the drop down box selections to make sure everything calculated and ran properly and it does.

Then I un-rem'd all the protection code..and protected the sheet. Made selections from the drop down list and only the top 2 subs run. Then I switched the order of the subs and still the top 2 subs ran. Test1 doesnt have any locking code in it so it always ran ok.

ex

[vba]
Private Sub TOLCHOICE_Change()

On Error Goto Exits
Application.EnableEvents = False

Call test1 ' ran
Call test2 ' ran
Call test3 ' didnt run


Exits:
Application.EnableEvents = True
End Sub
[/vba]

[vba]
Private Sub TOLCHOICE_Change()

On Error Goto Exits
Application.EnableEvents = False

Call test1 ' ran
Call test3 ' ran
Call test2 ' didnt run


Exits:
Application.EnableEvents = True
End Sub
[/vba]

Now im wondering if its because im locking and unlocking cells on a protected spreadsheet. Here is a portion of code in one of the subs.. Im locking an already locked cell...didnt see this error until i ran the sub via the editor... "unable to set the locked property of a range class".

So, if E12 is locked when when the spreadsheet is newly opened..and it goes thru the sub and tries to lock an already locked cell..i think that may be the problem? So when test2 ran...it crapped out before it runs test3?



[vba]
If Range("CB12") = "A3" Then
Range("E12") = Range("CG14")
[E12].Locked = True
[E12].Interior.ColorIndex = 39

'blah blah
[/vba]

[mdmackillop]

You could test first
If not ([E12]. locked) then [E12]. locked = true

[gimli]

SNAP!!

That was the ticket!! This place is awesome!

Didnt know you could test like that

thanks so much!

[mdmackillop]

You can also toggle between values
[E12]. locked =Not ([E12]. locked )

[gimli]

So many ways to skin the cat