|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 9th, 2007, 08:57 PM
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
March 10th, 2007, 08:11 AM
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
March 10th, 2007, 08:45 AM
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
March 10th, 2007, 01:45 PM
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
March 10th, 2007, 07:57 PM
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
March 10th, 2007, 08:29 PM
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
|
|
#7
March 10th, 2007, 08:51 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Locking/Unlocking cells upon condition
Typo there that would pose a problem.
Change A1:A100 to A1 only On Sat, 10 Mar 2007 12:29:39 -0800, Gord Dibben gorddibbATshawDOTca wrote: 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
|
|
#8
March 10th, 2007, 09:03 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Locking/Unlocking cells upon condition
Thanks Gord, sounds logical.
"Gord Dibben" wrote: Typo there that would pose a problem. Change A1:A100 to A1 only On Sat, 10 Mar 2007 12:29:39 -0800, Gord Dibben gorddibbATshawDOTca wrote: 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
|
|
#9
March 10th, 2007, 10:10 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Locking/Unlocking cells upon condition
Hey Gord, a little more help if you would. The workbook has three sheets, and
only the sheet 'PERSONAL' has sheetcode. All the locking/unlocking etc works in that sheet, and I then unprotect it to break links and a few other things (being careful to unprotect it before each break etc). The I activate the sheet 'NEW LIABILITIES' and execute the following code: 'COPY "LIABILITIES" FORMULAS Sheets("NEW LIABILITIES").Select Sheets("NEW LIABILITIES").Activate (this is probably unnecessary) Rows("60:238").Delete (this works and happens) ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollRow = 10 ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("J21").Select Now it does all of this except when it gets to protecting the sheet, when it trips over because of the code in the 'PERSONAL' sheet re locking cells in 'PERSONAL'. I don't understand why the code in 'PERSONAL' should have any effect on what I do in 'NEW LIABILITIES' This is the sheetcode for 'PERSONAL': Private Sub Worksheet_Calculate() ActiveSheet.UNPROTECT If Range("add.years.1").Value = 3 Then ActiveSheet.Range("previous.address.1").Locked = True Else ' 3 processing ActiveSheet.Range("previous.address.1").Locked = False End If If Range("yrs.position.1").Value = 3 Then ActiveSheet.Range("previous.job.1").Locked = True Else ' 3 processing ActiveSheet.Range("previous.job.1").Locked = False End If ActiveSheet.Protect End Sub It trips over when trying to protect 'LIABILITIES' and debugs to the line ActiveSheet.Range("previous.address.1").Locked = False from the sheetcode in 'PERSONAL'. EH? Thanks for your help, Brett
|
|
#10
March 10th, 2007, 11:24 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Locking/Unlocking cells upon condition
Glad you got going, and happy to see that Gord Dibben had my back during my
absence again. Be careful of what sheet you are referencing when inside one of the worksheet event handlers. A couple of things I see here that might need attention to: under your 'COPY "LIABILITIES" FORMULAS line you select and activate sheet NEW LIABILITIES and do some work on it but you don't unprotect it specifically before doing the work - even though you say things are working. When it errors out in that section, as I presume it is, if you hit the [Debug] button, which line of code is shaded (which indicates where the error is at). Is it the .Protect statement or the Range("J21").Select statement. If it's the .Select statement, the problem is most likely that J21 is Locked and the protection is set to not permit selecting Locked cells on protected sheets. You are correct, protecting/unprotecting any specific sheet should NOT affect the protected/unprotected state of any other sheet in the workbook. I will suggest a change to the code in the PERSONAL Worksheet_Calculate() event - change all instances of ActiveSheet. to ThisWorksheet. I'm a little concerned at this point because you say that the code you show is for the PERSONAL sheet, not the NEW LIABILITIES sheet. Was that a typo somewhere? If you are in any worksheet event handling code and need to do something with ranges in other worksheets, you must specifically point to them. For example, if you are in the Worksheet_Calculate() event for the PERSONAL worksheet and wish to examine/manipulate ranges on the NEW LIABILITIES sheet, then in the code you would need to reference them this way: Worksheets("NEW LIABILITIES").Range("previous.address.1").Locked = True So in a worksheet event handler, ActiveSheet. and/or ThisWorksheet. refer to the sheet that the code resides in, and if you need to reference another sheet in the workbook in that code, you must specify the sheet name. Hope that helps clear things up some. As Gord said earlier, there are several events for a worksheet that can be used for various things. Look at the code and view the pull-down list at the right side of the top of the code module window for a list of them (when Worksheet is displayed in the pull-down list just to it's left). Similarly, there are events that are associated with a workbook, such as BeforeSave and BeforeClose, Activate, Deactivate and more - just like each worksheet, this is a special module associated with the workbook vs worksheets and/or general purpose code. "Brettjg" wrote: Hey Gord, a little more help if you would. The workbook has three sheets, and only the sheet 'PERSONAL' has sheetcode. All the locking/unlocking etc works in that sheet, and I then unprotect it to break links and a few other things (being careful to unprotect it before each break etc). The I activate the sheet 'NEW LIABILITIES' and execute the following code: 'COPY "LIABILITIES" FORMULAS Sheets("NEW LIABILITIES").Select Sheets("NEW LIABILITIES").Activate (this is probably unnecessary) Rows("60:238").Delete (this works and happens) ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollRow = 10 ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("J21").Select Now it does all of this except when it gets to protecting the sheet, when it trips over because of the code in the 'PERSONAL' sheet re locking cells in 'PERSONAL'. I don't understand why the code in 'PERSONAL' should have any effect on what I do in 'NEW LIABILITIES' This is the sheetcode for 'PERSONAL': Private Sub Worksheet_Calculate() ActiveSheet.UNPROTECT If Range("add.years.1").Value = 3 Then ActiveSheet.Range("previous.address.1").Locked = True Else ' 3 processing ActiveSheet.Range("previous.address.1").Locked = False End If If Range("yrs.position.1").Value = 3 Then ActiveSheet.Range("previous.job.1").Locked = True Else ' 3 processing ActiveSheet.Range("previous.job.1").Locked = False End If ActiveSheet.Protect End Sub It trips over when trying to protect 'LIABILITIES' and debugs to the line ActiveSheet.Range("previous.address.1").Locked = False from the sheetcode in 'PERSONAL'. EH? Thanks for your help, Brett
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
