| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking/Unlocking cells upon condition
I have put the following code into the worksheet code but it doesn't work.
Can someone put me straight please? Private Sub PERSONAL_LOCK() If Range("add.years.1").Value < 3 Then ActiveSheet.UNPROTECT ActiveSheet.Range("add.prv.1:add.prv.yrs.2").Locke d = False ActiveSheet.Protect End If End Sub Thanks, Brett |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Locking/Unlocking cells upon condition
Go back and create another named range that includes the cell(s) now referred
to as add.prv.1 and add.priv.yrs.2 and then revise your .Locked statement. Lets say that add.prv.1 refers to A1 and add.priv.yrs.2 refers to B1. You could select A1 and B1 and give them a name like add.priv.yrs.all then use ActiveSheet.Range("add.priv.yrs.all").Locked = False An alternative is to break the statement into two statements: ActiveSheet.Range("add.priv.yrs.1").Locked = False ActiveSheet.Range("add.priv.yrs.2").Locked = False "Brettjg" wrote: I have put the following code into the worksheet code but it doesn't work. Can someone put me straight please? Private Sub PERSONAL_LOCK() If Range("add.years.1").Value < 3 Then ActiveSheet.UNPROTECT ActiveSheet.Range("add.prv.1:add.prv.yrs.2").Locke d = False ActiveSheet.Protect End If End Sub Thanks, Brett |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Locking/Unlocking cells upon condition
Hi JL, I've changed the code to this, but it still doesn't work. This is the
only code in the sheet code, nothing else at all. Am I missing some other code that it needs? Currently the value of "add.years.1" is 1 so the cells should be unlocked, but they are still locked. Private Sub PERSONAL_LOCK() If Range("add.years.1").Value = 3 Then ActiveSheet.UNPROTECT ActiveSheet.Range("add.prv.1").Locked = True ActiveSheet.Range("add.prv.yrs.1").Locked = True ActiveSheet.Range("add.prv.2").Locked = True ActiveSheet.Range("add.prv.yrs.2").Locked = True ActiveSheet.Protect End If End Sub "JLatham" wrote: Go back and create another named range that includes the cell(s) now referred to as add.prv.1 and add.priv.yrs.2 and then revise your .Locked statement. Lets say that add.prv.1 refers to A1 and add.priv.yrs.2 refers to B1. You could select A1 and B1 and give them a name like add.priv.yrs.all then use ActiveSheet.Range("add.priv.yrs.all").Locked = False An alternative is to break the statement into two statements: ActiveSheet.Range("add.priv.yrs.1").Locked = False ActiveSheet.Range("add.priv.yrs.2").Locked = False "Brettjg" wrote: I have put the following code into the worksheet code but it doesn't work. Can someone put me straight please? Private Sub PERSONAL_LOCK() If Range("add.years.1").Value < 3 Then ActiveSheet.UNPROTECT ActiveSheet.Range("add.prv.1:add.prv.yrs.2").Locke d = False ActiveSheet.Protect End If End Sub Thanks, Brett |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Locking/Unlocking cells upon condition
You say "sheet code" - so I am presuming you want this to work automatically
and detect when add.years.1 goes = 3 Then in the sheet's code you need to use the _Calculate() event processor to do this for you most likely, and I am also presuming you may want to toggle the .Locked state of those cells, so here's what code to work that way could look like: Private Sub Worksheet_Calculate() ActiveSheet.Unprotect If Range("add.years.1").Value < 3 Then ActiveSheet.Range("add.prv.yrs.1").Locked = False ActiveSheet.Range("add.prv.yrs.2").Locked = False Else '= 3 processing ActiveSheet.Range("add.prv.yrs.1").Locked = True ActiveSheet.Range("add.prv.yrs.2").Locked = True End If ActiveSheet.Protect End Sub If your worksheet is protected with a password, then you'll need to provide the password in the code, similiar to this: Private Sub Worksheet_Calculate() ActiveSheet.Unprotect Password:="mypassword" If Range("add.years.1").Value < 3 Then ActiveSheet.Range("add.prv.yrs.1").Locked = False ActiveSheet.Range("add.prv.yrs.2").Locked = False Else '= 3 processing ActiveSheet.Range("add.prv.yrs.1").Locked = True ActiveSheet.Range("add.prv.yrs.2").Locked = True End If ActiveSheet.Protect Password:="mypassword" End Sub "Brettjg" wrote: Hi JL, I've changed the code to this, but it still doesn't work. This is the only code in the sheet code, nothing else at all. Am I missing some other code that it needs? Currently the value of "add.years.1" is 1 so the cells should be unlocked, but they are still locked. Private Sub PERSONAL_LOCK() If Range("add.years.1").Value = 3 Then ActiveSheet.UNPROTECT ActiveSheet.Range("add.prv.1").Locked = True ActiveSheet.Range("add.prv.yrs.1").Locked = True ActiveSheet.Range("add.prv.2").Locked = True ActiveSheet.Range("add.prv.yrs.2").Locked = True ActiveSheet.Protect End If End Sub "JLatham" wrote: Go back and create another named range that includes the cell(s) now referred to as add.prv.1 and add.priv.yrs.2 and then revise your .Locked statement. Lets say that add.prv.1 refers to A1 and add.priv.yrs.2 refers to B1. You could select A1 and B1 and give them a name like add.priv.yrs.all then use ActiveSheet.Range("add.priv.yrs.all").Locked = False An alternative is to break the statement into two statements: ActiveSheet.Range("add.priv.yrs.1").Locked = False ActiveSheet.Range("add.priv.yrs.2").Locked = False "Brettjg" wrote: I have put the following code into the worksheet code but it doesn't work. Can someone put me straight please? Private Sub PERSONAL_LOCK() If Range("add.years.1").Value < 3 Then ActiveSheet.UNPROTECT ActiveSheet.Range("add.prv.1:add.prv.yrs.2").Locke d = False ActiveSheet.Protect End If End Sub Thanks, Brett |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Locking/Unlocking cells upon condition
Crackajack JL, it works beautifully. Thank you so much for your help. I also
tried another If after the first one for a different test and range and that was successful as well. Do I presume that anything I want to do like this has to be in the same Private Sub which must be called Worksheet_Calculate? "JLatham" wrote: You say "sheet code" - so I am presuming you want this to work automatically and detect when add.years.1 goes = 3 Then in the sheet's code you need to use the _Calculate() event processor to do this for you most likely, and I am also presuming you may want to toggle the .Locked state of those cells, so here's what code to work that way could look like: Private Sub Worksheet_Calculate() ActiveSheet.Unprotect If Range("add.years.1").Value < 3 Then ActiveSheet.Range("add.prv.yrs.1").Locked = False ActiveSheet.Range("add.prv.yrs.2").Locked = False Else '= 3 processing ActiveSheet.Range("add.prv.yrs.1").Locked = True ActiveSheet.Range("add.prv.yrs.2").Locked = True End If ActiveSheet.Protect End Sub If your worksheet is protected with a password, then you'll need to provide the password in the code, similiar to this: Private Sub Worksheet_Calculate() ActiveSheet.Unprotect Password:="mypassword" If Range("add.years.1").Value < 3 Then ActiveSheet.Range("add.prv.yrs.1").Locked = False ActiveSheet.Range("add.prv.yrs.2").Locked = False Else '= 3 processing ActiveSheet.Range("add.prv.yrs.1").Locked = True ActiveSheet.Range("add.prv.yrs.2").Locked = True End If ActiveSheet.Protect Password:="mypassword" End Sub "Brettjg" wrote: Hi JL, I've changed the code to this, but it still doesn't work. This is the only code in the sheet code, nothing else at all. Am I missing some other code that it needs? Currently the value of "add.years.1" is 1 so the cells should be unlocked, but they are still locked. Private Sub PERSONAL_LOCK() If Range("add.years.1").Value = 3 Then ActiveSheet.UNPROTECT ActiveSheet.Range("add.prv.1").Locked = True ActiveSheet.Range("add.prv.yrs.1").Locked = True ActiveSheet.Range("add.prv.2").Locked = True ActiveSheet.Range("add.prv.yrs.2").Locked = True ActiveSheet.Protect End If End Sub "JLatham" wrote: Go back and create another named range that includes the cell(s) now referred to as add.prv.1 and add.priv.yrs.2 and then revise your .Locked statement. Lets say that add.prv.1 refers to A1 and add.priv.yrs.2 refers to B1. You could select A1 and B1 and give them a name like add.priv.yrs.all then use ActiveSheet.Range("add.priv.yrs.all").Locked = False An alternative is to break the statement into two statements: ActiveSheet.Range("add.priv.yrs.1").Locked = False ActiveSheet.Range("add.priv.yrs.2").Locked = False "Brettjg" wrote: I have put the following code into the worksheet code but it doesn't work. Can someone put me straight please? Private Sub PERSONAL_LOCK() If Range("add.years.1").Value < 3 Then ActiveSheet.UNPROTECT ActiveSheet.Range("add.prv.1:add.prv.yrs.2").Locke d = False ActiveSheet.Protect End If End Sub Thanks, Brett |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Locking/Unlocking cells upon condition
You can have multiple event handlers in a sheet module, but not multiples of one
type of event. i.e. one Worksheet_Calculate event per sheet. That one event could call various macros or move to statements based on the value of a cell or cells. Or just expand upon the conditions in Jerry's code. e.g. using Select Case method to choose what to do when a condition is met with your Worksheet_Calculate event. Example of Select Case code.......... Private Sub Worksheet_Calculate() Dim Target As Range For Each Target In Me.Range("A1:A100") With Target Select Case .Value Case Is = 3: mymacro3 Case Is = 1: mymacro1 Case Is = 2: mymacro2 Case Is = 4: mymacro4 'etc. End Select End With Next Target End Sub Gord Dibben MS Excel MVP On Sat, 10 Mar 2007 11:57:07 -0800, Brettjg wrote: Crackajack JL, it works beautifully. Thank you so much for your help. I also tried another If after the first one for a different test and range and that was successful as well. Do I presume that anything I want to do like this has to be in the same Private Sub which must be called Worksheet_Calculate? "JLatham" wrote: You say "sheet code" - so I am presuming you want this to work automatically and detect when add.years.1 goes = 3 Then in the sheet's code you need to use the _Calculate() event processor to do this for you most likely, and I am also presuming you may want to toggle the .Locked state of those cells, so here's what code to work that way could look like: Private Sub Worksheet_Calculate() ActiveSheet.Unprotect If Range("add.years.1").Value < 3 Then ActiveSheet.Range("add.prv.yrs.1").Locked = False ActiveSheet.Range("add.prv.yrs.2").Locked = False Else '= 3 processing ActiveSheet.Range("add.prv.yrs.1").Locked = True ActiveSheet.Range("add.prv.yrs.2").Locked = True End If ActiveSheet.Protect End Sub If your worksheet is protected with a password, then you'll need to provide the password in the code, similiar to this: Private Sub Worksheet_Calculate() ActiveSheet.Unprotect Password:="mypassword" If Range("add.years.1").Value < 3 Then ActiveSheet.Range("add.prv.yrs.1").Locked = False ActiveSheet.Range("add.prv.yrs.2").Locked = False Else '= 3 processing ActiveSheet.Range("add.prv.yrs.1").Locked = True ActiveSheet.Range("add.prv.yrs.2").Locked = True End If ActiveSheet.Protect Password:="mypassword" End Sub "Brettjg" wrote: Hi JL, I've changed the code to this, but it still doesn't work. This is the only code in the sheet code, nothing else at all. Am I missing some other code that it needs? Currently the value of "add.years.1" is 1 so the cells should be unlocked, but they are still locked. Private Sub PERSONAL_LOCK() If Range("add.years.1").Value = 3 Then ActiveSheet.UNPROTECT ActiveSheet.Range("add.prv.1").Locked = True ActiveSheet.Range("add.prv.yrs.1").Locked = True ActiveSheet.Range("add.prv.2").Locked = True ActiveSheet.Range("add.prv.yrs.2").Locked = True ActiveSheet.Protect End If End Sub "JLatham" wrote: Go back and create another named range that includes the cell(s) now referred to as add.prv.1 and add.priv.yrs.2 and then revise your .Locked statement. Lets say that add.prv.1 refers to A1 and add.priv.yrs.2 refers to B1. You could select A1 and B1 and give them a name like add.priv.yrs.all then use ActiveSheet.Range("add.priv.yrs.all").Locked = False An alternative is to break the statement into two statements: ActiveSheet.Range("add.priv.yrs.1").Locked = False ActiveSheet.Range("add.priv.yrs.2").Locked = False "Brettjg" wrote: I have put the following code into the worksheet code but it doesn't work. Can someone put me straight please? Private Sub PERSONAL_LOCK() If Range("add.years.1").Value < 3 Then ActiveSheet.UNPROTECT ActiveSheet.Range("add.prv.1:add.prv.yrs.2").Locke d = False ActiveSheet.Protect End If End Sub Thanks, Brett |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unlocking protected cells | Excel Worksheet Functions | |||
| Unlocking cells | Excel Worksheet Functions | |||
| locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
| conditional unlocking of cells | Excel Worksheet Functions | |||
| Unlocking Cells when a worksheet is protected... | Excel Discussion (Misc queries) | |||
Hybrid Mode
