Case Statement - help required
 

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

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

Case Statement - help required
 

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

Case Statement - help required
 

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

Case Statement - help required
 

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

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

Case Statement - help required
 

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" wrote in
message ...

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

Case Statement - help required
 

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


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