| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 t get to grips with it - anyway, here is what I am trying to do: Lock cells conditionally using Select Case but my attempt below jus doesn't work - my condition is as follows If cell $C$1 is not equal to "Yes" then lock the cel 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").Locke = 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").Locke = 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").Locke = 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").Locke = 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").Locke = 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 t "Yes" I would be grateful for any help - just like a cry from th wilderness! Deele -- deele ----------------------------------------------------------------------- deelee's Profile: http://www.excelforum.com/member.php...fo&userid=3486 View this thread: http://www.excelforum.com/showthread.php?threadid=54614 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Cell Locking/Unlocking
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$3 1,$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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Cell Locking/Unlocking
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" wrote in message oups.com... 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$3 1,$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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Cell Locking/Unlocking
:) 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 -- deelee ------------------------------------------------------------------------ deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866 View this thread: http://www.excelforum.com/showthread...hreadid=546140 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Cell Locking/Unlocking
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Cell Locking/Unlocking
:) 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 -- deelee ------------------------------------------------------------------------ deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866 View this thread: http://www.excelforum.com/showthread...hreadid=546140 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Locking/Unlocking based on another cell value | Excel Worksheet Functions | |||
| locking and unlocking worksheets | Excel Worksheet Functions | |||
| Unlocking and locking a specific cell | Excel Programming | |||
| Locking/unlocking cells | Excel Programming | |||
| locking and unlocking a row of data based on whats entered in a cell | Excel Programming | |||
![deelee[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
