Conditional Cell Locking/Unlocking

Could someone please help!?

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

Lock cells conditionally using Select Case but my attempt below just doesn't work - my condition is as follows
If cell $C$1 is not equal to "Yes" then lock the cell 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").Locked = 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").Locked = 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").Locked = 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").Locked = 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").Locked = 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 to "Yes"

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

Deelee

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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

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