Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Satement
I've been trying, unsuccessfully, to modified a case statemen that I was kindly helped with some weeks ago. My cry for help at tha time was a resounding success and I am hoping for a similar resul today! One of my statements is: Case "$C$3" If Target.Value = "No" Then ActiveSheet.Unprotect ("mypassword") Range("C:C").Locked = True Range("$C$3").Locked = False ActiveSheet.Protect ("mypassword") 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").Locke = False ActiveSheet.Protect ("mypassword") End If Which works just as it should, even after me playing with it! however I now find that not all of the ranges in line 8 need to remain locke but the pattern is random across twelve worksheets! I have a cell which looksup if a range needs to be locked and in th case of the first range ($C$6:$C$10) it is cell $C$4$ - this cell i relative in each other case and if the range needs to be locked th cell will hold "PH". Using this, Conditional Formatting changes th appearance of the ranges. (Why couldn't cell locking be a feature o Conditional Formatting? - no doubt someone will be able to enlighten m :)). I have tried using Offset() and Resize() from the active cell withou success and was hoping one or two of you kind people would help m out! Thanks in advance, Dav -- deele ----------------------------------------------------------------------- deelee's Profile: http://www.excelforum.com/member.php...fo&userid=3486 View this thread: http://www.excelforum.com/showthread.php?threadid=55076 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Satement
It's me again! I've played around with the statement but still can't get it to work! I thought it might help if I post my latest attempt so that you can see where I'm going wrong! 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=550762 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change data of entire column from small case to upper case | Excel Worksheet Functions | |||
If Satement formula | Excel Worksheet Functions | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
If Satement Modification | Excel Programming |