Case Statement - help required

[deelee]

I'm aware I seem to be double posting but I'm desperate for help wit
this script.

My problem is the second ElseIf - it doesn't do what I think it shoul
- perhaps what I'm doing is classed an illegal?

Your help and advice is always welcome:

Case "$C$3" 'Monday

'Non-working day
If Target.Value = "No" Then
ActiveSheet.Unprotect ("MyPassword")
Range("C:C").Locked = True
Range("$C$3").Locked = False
ActiveSheet.Protect ("MyPassword")

'Working Day
ElseIf Target.Value = "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$C$6:$C$10,$C$16:$C$20,$C$26:$C$30,$C$36:$
$40,$C$46:$C$50").Locked = False
ActiveSheet.Protect ("MyPassword")

'Public Holiday
ElseIf Target.Offset(1, 0) = "PH" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$C$6:$C$10").Locked = True
ActiveSheet.Protect ("MyPassword")

EndIf

Thanks again

Dav

 

[Guest]

Assuming you don't have merged cells involved, then your code in all 3
instances appears to be consistent. If one works, they all should work.

further assuming your case is promulgated on the address of Target, then if
Target is a single cell, $C$3 then,

If the ElseIf in question never gets called, then
a) does C4 contain only two characters and those characters are "PH" (case
sensitive)
and C3 contains neither "Yes" nor "No"

 

[deelee]

Hi Tom,

Thanks for your quick reply..

Cell C3 can only be "Yes" or "No" and the Offset does hold "PH",
however, "PH" is derived from a lookup formula in that cell - would
this compromise the script?

Dave

 

[deelee]

I've answered my own question - removed the lookup and input "PH" but i
still doesn't work - I'm at a bit of an impasse - they say a littl
knowledge is dangerous ...

Regards,

Dav

 

[Tom Ogilvy]

If C3 has only Yes or No in it, then it will be handled by one of your first
two conditions and never get to the third. I tried to explain that in my
original post.

You might need to rethink your logic, because it isn't clear what the
possible options are.

 

[deelee]

Hi Tom,

Once again, thanks for your input and advice. It wasn't 'til late last
night that I got the gist of your initial reply - I'm not blessed with
the quickest of minds!

I re-visited my original logic and found it lacking. After a couple of
false starts I got what I wanted and more, so thanks for your patience
and sound advice - I'll be able to stand on my own two feet in a couple
of years, honest!

Thanks again,

Regards,

Dave