Conditional Cell Locking/Unlocking
 

:confused: Could someone please help!?

I am afraid VBA is a mystery to me and I just don't seem to be able t
get to grips with it - anyway, here is what I am trying to do:

Lock cells conditionally using Select Case but my attempt below jus
doesn't work - my condition is as follows

If cell $C$1 is not equal to "Yes" then lock the cel
ranges as listed:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address

Case "$C$1" < "Yes"
ActiveSheet.Unprotect ("MyPassword")
Range("$C$4:$C$7,$C$12:$C$15,$C$20:$C$23,$C$28:$C$ 31,$C$36:$C$39").Locke
= True
ActiveSheet.Protect ("MyPassword")

Case "$D$1" < "Yes"
ActiveSheet.Unprotect ("MyPassword")
Range("$D$4:$D$7,$D$12:$D$15.$D$20:$D$23:,$D$28:$D $31,$D$36:$D$39").Locke
= True
ActiveSheet.Protect ("MyPassword")

Case "$E$1" < "Yes"
ActiveSheet.Unprotect ("MyPassword")
Range("$E$4:$E$7,$E$12:$E$15.$E20:$E$23:,$E$28:$E$ 31,$E$36:$E$39").Locke
= True
ActiveSheet.Protect ("MyPassword")

Case "$F$1" < "Yes"
ActiveSheet.Unprotect ("MyPassword")
Range("$F$4:$F$7,$F$12:$F$15.$F$20:$F$23:,$F$28:$F $31,$F$36:$F$39").Locke
= True
ActiveSheet.Protect ("MyPassword")

Case "$G$1" < "Yes"
ActiveSheet.Unprotect ("MyPassword")
Range("$G$4:$G$7,$G$12:$G$15.$G$20:$G$23:,$G$28:$G $31,$G$36:$G$39").Locke
= True
ActiveSheet.Protect ("MyPassword")

Case Else
Exit Sub

End Select
End Sub

If possible I would also like to Unlock these cells if $C$1 equal t
"Yes"

I would be grateful for any help - just like a cry from th
wilderness!

Deele

--
deele
-----------------------------------------------------------------------
deelee's Profile: http://www.excelforum.com/member.php...fo&userid=3486
View this thread: http://www.excelforum.com/showthread.php?threadid=54614

Conditional Cell Locking/Unlocking
 

Hi Deelee,

does this do what you want?...

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$C$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$C$4:$C$7,$C$12:$C$15,$C$20:$C$23,$C$28:$C$ 31,$C$36:$C$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$D$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$D$4:$D$7,$D$12:$D$15,$D$20:$D$23,$D$28:$D$ 31,$D$36:$D$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$E$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$E$4:$E$7,$E$12:$E$15,$E20:$E$23,$E$28:$E$3 1,$E$36:$E$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$F$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$F$4:$F$7,$F$12:$F$15,$F$20:$F$23,$F$28:$F$ 31,$F$36:$F$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$G$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$G$4:$G$7,$G$12:$G$15,$G$20:$G$23,$G$28:$G$ 31,$G$36:$G$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case Else
Exit Sub

End Select
End Sub

There were a few typos in the Range addresses too eg "." instead of ","
and ":," instead of ",".


Ken Johnson

Conditional Cell Locking/Unlocking
 

DeeLee, Ken's solution should work for you if < "Yes". To answer your 2nd
question about unlocking If ="Yes", I have added that code for Case $C$1,
you can fill in the rest.

Case "$C$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$C$4:$C$7,$C$12:$C$15,$C$20:$C$23,$C$28:$C$ 31,$C$36:$C$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
ElseIf Target.Value = "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$C$4:$C$7,$C$12:$C$15,$C$20:$C$23,$C$28:$C$ 31,$C$36:$C$39"). _
Locked = False
ActiveSheet.Protect ("MyPassword")
End If

Mike F
"Ken Johnson" wrote in message
oups.com...
Hi Deelee,

does this do what you want?...

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$C$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$C$4:$C$7,$C$12:$C$15,$C$20:$C$23,$C$28:$C$ 31,$C$36:$C$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$D$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$D$4:$D$7,$D$12:$D$15,$D$20:$D$23,$D$28:$D$ 31,$D$36:$D$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$E$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$E$4:$E$7,$E$12:$E$15,$E20:$E$23,$E$28:$E$3 1,$E$36:$E$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$F$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$F$4:$F$7,$F$12:$F$15,$F$20:$F$23,$F$28:$F$ 31,$F$36:$F$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$G$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$G$4:$G$7,$G$12:$G$15,$G$20:$G$23,$G$28:$G$ 31,$G$36:$G$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case Else
Exit Sub

End Select
End Sub

There were a few typos in the Range addresses too eg "." instead of ","
and ":," instead of ",".


Ken Johnson

Conditional Cell Locking/Unlocking
 

:) Thanks Ken - I've been meaning to change these glasses for an age
now!

To be quite truthful, I'm feeling pleased with myself for getting it so
close - I'll just have to go out and by a better book!

Thanks again,

Dave


--
deelee
------------------------------------------------------------------------
deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866
View this thread: http://www.excelforum.com/showthread...hreadid=546140

Conditional Cell Locking/Unlocking
 

You're welcome Dave.
Thanks for the feedback.

I guess I need new glasses too, I missed you're last request that Mike
picked up.

Ken Johnson

Conditional Cell Locking/Unlocking
 

:) Hi Mike - thanks for your input and it works a treat!

I was in the process of trying to mod Ken's script and again I nearly
had it but tried to insert another 'Else'. Of course, it didn't work
and being a bit of a dumbo didn't even consider ElseIf!

For some strange reason the Case cell (($C$3), the original was ($C$1)
but I changed the worksheet!) locked as well, but I added:

Range("$C$3").Locked = False

and sorted it - I didn't think I had it in me:)

Thanks again to the both of you

Dave


--
deelee
------------------------------------------------------------------------
deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866
View this thread: http://www.excelforum.com/showthread...hreadid=546140