Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#11
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Is the code you posted a macro that you run manually on the NEW LIABILITIES
sheet? Post the entire code between Sub mymacro() and End Sub Don't forget that you have Worksheet_Calculate event code in PERSONAL sheet which will be firing if the application calculates. That code is looking for the named ranges which don't exist because the activesheet is now NEW LIABILITIES. I am not sure how to overcome that problem other than to turn calculation to manual when you switch sheets. Gord On Sat, 10 Mar 2007 14:10:03 -0800, 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 |
#12
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Or as Jerry points out........use ThisWorksheet instead of Activesheet.
Gord On Sat, 10 Mar 2007 15:36:35 -0800, Gord Dibben <gorddibbATshawDOTca wrote: I am not sure how to overcome that problem other than to turn calculation to manual when you switch sheets. |
#13
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Jerry, I think my head hurts. I'll explain the sequence of events.
1. I open up the file in it's master state (no client data). 2. I enter the client name which creates paths for the folder and file to follow later 3. I click a button to select one of six macros to run, depending on client type 4. The macro from 3. (above) performs varios functions on the three sheets in the book to get into the state I require for the client type (different formulas, deletes rows etc) 5. I then save the file in the client's folder and email it out to them so that they can enter data and send it back to me. The lock/unlock cells routine is so that the client can't enter unnecessary data (e.g if at the same address for 3 years I don't need the previous address and so the cells for the previous address get locked). This of course has to be in the sheet code for 'PERSONAL' which is now as follows: Private Sub Worksheet_Calculate() ActiveSheet.UNPROTECT If Range("add.years.1").Value = 3 Then Range("previous.address.1").Locked = True Else '< 3 processing Range("previous.address.1").Locked = False ' IT TRIPS UP ON THE LINE ABOVE when I hit the macro button End If If Range("yrs.position.1").Value = 3 Then Range("previous.job.1").Locked = True Else '< 3 processing Range("previous.job.1").Locked = False End If If Range("nm.first.2").Value < 1 Then Range("client.2.1").Locked = True Range("client.2.2").Locked = True Else '< 3 processing Range("client.2.1").Locked = False Range("client.2.2").Locked = False ActiveWindow.ScrollRow = 9 If Range("add.years.2").Value = 3 Then Range("previous.address.2").Locked = True Else '< 3 processing Range("previous.address.2").Locked = False End If If Range("yrs.position.2").Value = 3 Then Range("previous.job.2").Locked = True Else '< 3 processing Range("previous.job.2").Locked = False End If End If ActiveSheet.Protect End Sub You will note that I have removed Activesheet altogether, and not replaced it with ThisWorksheet. It seems unnecessary, and it does work like this. It did NOT like having ThisWorksheet.UNPROTECT as the second line (it didn't seem to know what I meant). So far all that works when I pretend to be the client filling in data, but this step actually comes after I hit the macro button to set up the client type. The code for that particular button is as follows: Sub NEW_CLIENT() Sheets("Instructions").Visible = True Sheets("Privacy Act").Visible = True Sheets("PERSONAL").Select ActiveSheet.UNPROTECT Range("virgin").FormulaR1C1 = "" Application.DisplayAlerts = False ActiveSheet.UNPROTECT ActiveWorkbook.BreakLink Name:=Range("Falsepath.97PREVIOUS"), Type:=xlExcelLinks ActiveSheet.UNPROTECT ActiveWorkbook.BreakLink Name:=Range("Path.DATAFILE"), Type:=xlExcelLinks ActiveSheet.UNPROTECT ActiveWorkbook.BreakLink Name:=Range("Falsepath.SNAPSHOT"), Type:=xlExcelLinks ActiveSheet.UNPROTECT ActiveWorkbook.BreakLink Name:=Range("Path.DD"), Type:=xlExcelLinks ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Application.DisplayAlerts = True 'COPY "LIABILITIES" FORMULAS Sheets("NEW LIABILITIES").Select Sheets("NEW LIABILITIES").Activate Rows("60:238").Delete ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollRow = 10 'IT WORKS FINE UNTIL THE FOLLOWING LINE: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("J21").Select BLAH BLAH BLAH to End Sub When it gets to the ActiveSheet.Protect it fails , but it debugs to the Private Sub in 'PERSONAL' even though its on the 'NEW LIABILITIES ' sheet (I have marked as "IT TRIPS UP ON THE LINE ABOVE". There is no other sheet code in any other sheet except 'PERSONAL' I know that's a lot to digest for you. In other words I'm executing a macro that tootles off to another sheet, does most of it's stuff, but fails when it tries to protect that sheet (not 'PERSONAL'), but it debugs to the sheetcode in 'PERSONAL'. I have changed the Activesheet to be 'NEW LIABILITIES' as you can see. Jerry, your help is greatly appreciated. Regards, Brett |
#14
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hey Jerry, the other thing is: Can ThisWorksheet be used to refer to more
than one worksheet. That is if 'PERSONAL' is active I can refer to it as ThisWorksheet, but can I then activate 'NEW LIABLITIES' and refer to that as ThisWorksheet as well? Thanks, Brett |
#15
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Gord, thanks for your help here. I've posted back to Jerry with a full
explanation of the sequence of events for better understanding. I've been pusuing the ThisWorksheet idea, but the Private Sub didn't like it. Regards, Brett "Gord Dibben" wrote: Is the code you posted a macro that you run manually on the NEW LIABILITIES sheet? Post the entire code between Sub mymacro() and End Sub Don't forget that you have Worksheet_Calculate event code in PERSONAL sheet which will be firing if the application calculates. That code is looking for the named ranges which don't exist because the activesheet is now NEW LIABILITIES. I am not sure how to overcome that problem other than to turn calculation to manual when you switch sheets. Gord On Sat, 10 Mar 2007 14:10:03 -0800, 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 |
#16
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
ThisWorksheet can refer to more than one worksheet, but only one sheet at a
time. It's all related to what Gord explained earlier. You have Event processing code for a worksheet - typically you only do things to that worksheet while in it's event code, although you may do others. But while in any given sheet's event code, ThisWorksheet will refer to the sheet that the code is associated with. And it should remain that way even if another sheet is activated for some reason and the code still has work to do. Most of the time ActiveSheet will also refer to that same sheet while in one of the sheet's event processors - but that's not always guaranteed. If you get in the middle of a module for PERSONAL and use a command such as Worksheets("NEW LIABILITIES").Activate then ActiveSheet will refer to NEW LIABILITIES while ThisWorksheet would still refer to PERSONAL. If you switch sheets in the middle of one of these and then try to reference a range that on the sheet with code running without specifying which sheet (or the wrong sheet) then you'll error out. I'm wondering if we haven't started down the wrong path for this, and the aggregate changes throughout the workbook aren't really ripping at our throats. From reading the explanation 2 posts up, I see you really don't need any of this to happen until you're done messing with the workbook and ready to email it to your client. I suggest we just get rid of all of this Worksheet_... stuff and do your processing in one of the WORKBOOK related events, such as Workbook_BeforeSave() In that area there is no such thing as ThisWorksheet, and you'll want to stay away from ActiveSheet also; sticking with explicitly naming the sheets and the ranges on them that are to be altered/protected. Also, in there you'll probably want to start off the code with Application.EnableEvents = FALSE and end it with Application.EnableEvents = TRUE That prevents the changes you'll make in the code from triggering other events, like the Worksheet_Change() etc. To get to the Workbook equivalent of the Worksheet code modules, easy way is to right click on the Excel Icon immediately to the left of the File option in the menu bar and choose [View Code] from it's list. I'm going to put up untested code here to give you an idea of what it could look like. Notice that I've used With statements referring to various worksheets - change the names to be correct with the specific ranges used in the code between the various With ... End With pairings. Also, note that the properties involved are all preceded with a dot, like ..Range(...) instead of just Range(...) That's because we are using With...End With which keeps us from having to type Worksheet('somesheetname').Range(...) over and over. If I missed one, there should NOT be any ActiveSheet. or ThisWorksheet. references in this code at all: Might want to make a new copy of the workbook, go in and just delete all of the Worksheet_Change() event handling for all sheets (select and delete, that simple) and put this in the Workbook_BeforeSave() event and see if that doesn't serve you better. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.EnableEvents = False With Worksheets("PERSONAL") ' change name appropriately .Unprotect If .Range("add.years.1").Value < 3 Then .Range("add.prv.yrs.1").Locked = False .Range("add.prv.yrs.2").Locked = False Else '= 3 processing .Range("add.prv.yrs.1").Locked = True .Range("add.prv.yrs.2").Locked = True End If .Protect End With With Worksheets("NEW LIABILITIES") ' change name appropriately If .Range("yrs.position.1").Value = 3 Then .Range("previous.job.1").Locked = True Else '< 3 processing .Range("previous.job.1").Locked = False End If End With With Worksheets("NEW CLIENT") ' change name appropriately If Range("nm.first.2").Value < 1 Then .Range("client.2.1").Locked = True .Range("client.2.2").Locked = True Else '< 3 processing .Range("client.2.1").Locked = False .Range("client.2.2").Locked = False If Range("add.years.2").Value = 3 Then .Range("previous.address.2").Locked = True Else '< 3 processing .Range("previous.address.2").Locked = False End If If Range("yrs.position.2").Value = 3 Then .Range("previous.job.2").Locked = True Else '< 3 processing .Range("previous.job.2").Locked = False End If End If End With Application.EnableEvents = True End Sub "Brettjg" wrote: Hey Jerry, the other thing is: Can ThisWorksheet be used to refer to more than one worksheet. That is if 'PERSONAL' is active I can refer to it as ThisWorksheet, but can I then activate 'NEW LIABLITIES' and refer to that as ThisWorksheet as well? Thanks, Brett |
#17
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
See my new suggestion above - after your last explanation, I'm thinking you
don't need to worry with this until you're finished working with the file and are ready to send it off to the client. In which case, we can probably get away from the confusion caused by multiple sheets trying to deal with Change() seemingly simultaneously - and do the coding as a final step before the file is saved using the Workbook_BeforeSave(...) event. I'm not proud - I know when to retreat when an idea seems to have not panned out. It often happens around he you get one set of specs and come up with a solution only to find there's a "Rest of the Story" waiting. No big deal - it happens in more cases than it doesn't. Hope this helps some, and my thanks to Gord for jumping in with his good ideas also (I learn a lot when he does that) - and maybe he'll have a different one than my track-jumping idea to deal with this also. "Brettjg" wrote: Hi Gord, thanks for your help here. I've posted back to Jerry with a full explanation of the sequence of events for better understanding. I've been pusuing the ThisWorksheet idea, but the Private Sub didn't like it. Regards, Brett "Gord Dibben" wrote: Is the code you posted a macro that you run manually on the NEW LIABILITIES sheet? Post the entire code between Sub mymacro() and End Sub Don't forget that you have Worksheet_Calculate event code in PERSONAL sheet which will be firing if the application calculates. That code is looking for the named ranges which don't exist because the activesheet is now NEW LIABILITIES. I am not sure how to overcome that problem other than to turn calculation to manual when you switch sheets. Gord On Sat, 10 Mar 2007 14:10:03 -0800, 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 |
#18
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
JERRY! YEEEEEEEEEEEEeeeeeeeeeeeeeeeeeeeeaaaaaaaaaaaaaaaaa hhhhhhhhhhhhh!
In the end there's no ThisWorksheet, no Activesheet, just specific names. I'm using the original code again, but like this. What a slog. Thanks very much for your help and persistance. Brett |
#19
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I know I speak for myself, and I think maybe for Gord also when I say
Thank you for the Thank you. All's well that ends well, and it was probably a learning experience for all concerned. Sometimes you just have to turn around, make three left turns instead of a single right turn to get to the end of the block - "Brettjg" wrote: JERRY! YEEEEEEEEEEEEeeeeeeeeeeeeeeeeeeeeaaaaaaaaaaaaaaaaa hhhhhhhhhhhhh! In the end there's no ThisWorksheet, no Activesheet, just specific names. I'm using the original code again, but like this. What a slog. Thanks very much for your help and persistance. Brett |
#20
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Jerry and Brett
You seem to have resolved the problem. Brett.........for more information, Chip Pearson has good coverage on Events at his site. http://www.cpearson.com/excel/events.htm Scroll down to see "enabling and disabling events" Gord On Sat, 10 Mar 2007 20:21:15 -0800, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: I know I speak for myself, and I think maybe for Gord also when I say Thank you for the Thank you. All's well that ends well, and it was probably a learning experience for all concerned. Sometimes you just have to turn around, make three left turns instead of a single right turn to get to the end of the block - "Brettjg" wrote: JERRY! YEEEEEEEEEEEEeeeeeeeeeeeeeeeeeeeeaaaaaaaaaaaaaaaaa hhhhhhhhhhhhh! In the end there's no ThisWorksheet, no Activesheet, just specific names. I'm using the original code again, but like this. What a slog. Thanks very much for your help and persistance. Brett |
#21
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Yes Gord, thanks for your help. Sorry mate, I didn't see this last post of
yours until just now. It seems to me (from Jerry's response) that you guys don't get much thanks, and that sucks if it's the case. Your a bunch of lifesavers. Cheers, Brett "Gord Dibben" wrote: Jerry and Brett You seem to have resolved the problem. Brett.........for more information, Chip Pearson has good coverage on Events at his site. http://www.cpearson.com/excel/events.htm Scroll down to see "enabling and disabling events" Gord On Sat, 10 Mar 2007 20:21:15 -0800, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: I know I speak for myself, and I think maybe for Gord also when I say Thank you for the Thank you. All's well that ends well, and it was probably a learning experience for all concerned. Sometimes you just have to turn around, make three left turns instead of a single right turn to get to the end of the block - "Brettjg" wrote: JERRY! YEEEEEEEEEEEEeeeeeeeeeeeeeeeeeeeeaaaaaaaaaaaaaaaaa hhhhhhhhhhhhh! In the end there's no ThisWorksheet, no Activesheet, just specific names. I'm using the original code again, but like this. What a slog. Thanks very much for your help and persistance. Brett |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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) |