Thread: Case Statement - help required

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

My problem is the second ElseIf - it doesn't do what I think it should - 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:$C $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

Dave

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"

--
Regards,
Tom Ogilvy


"deelee" wrote:

>
> I'm aware I seem to be double posting but I'm desperate for help with
> this script.
>
> My problem is the second ElseIf - it doesn't do what I think it should
> - 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:$C
> $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
>
> Dave
>
>
> --
> deelee
> ------------------------------------------------------------------------
> deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866
> View this thread: http://www.excelforum.com/showthread...hreadid=551127
>
>

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

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

Regards,

Dave

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.

--
Regards,
Tom Ogilvy


"deelee" <deelee.29b2vi_1150143683.7884@excelforum-nospam.com> wrote in
message news:deelee.29b2vi_1150143683.7884@excelforum-nospam.com...
>
> 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
> ------------------------------------------------------------------------
> deelee's Profile:
http://www.excelforum.com/member.php...o&userid=34866
> View this thread: http://www.excelforum.com/showthread...hreadid=551127
>

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