Conditional Cell Locking/Unlocking

[deelee]

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

 

[Ken Johnson]

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$31,$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

 

[Mike Fogleman]

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

 

[deelee]

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

 

[Ken Johnson]

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

 

[deelee]

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