Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Worksheet_Change call itself?
Hi,
In my Worksheet_Change event, I would like to change a cell and call the Worksheet_Change event again. In B3, the user enters a number up to 9, to access up to 9 vehicles. If the old value was 3 and the user enters 5, then the Worksheet_Change acknowledges the change and Displays some check boxes (Visible = True) for the 4th and 5th column. The check boxes were hidden when B3 originally went smaller than 5 (Visible = False). The ability to use these check boxes also depends on the entry in row 16. So first, I make the check boxes for vehicles 4 and 5 visible, and then want to re-enter the entry in row 16 for these 2 vehicles. I thought if I just set a cell to a value, it would initiate the Worksheet_Change event and the new Target would be the cell in row 16. In a For loop (For X = 4 to 5, in this case), I have Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value Part of my Worksheet_Change event includes an intersection of Target and "B16:J16", however the code after this intersection is not running. What are my options to be able to run the code? -- Thanks, Brad E. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Worksheet_Change call itself?
Brad,
You'l l see what's wrong with that approach if you run this snippet of code by changing a cell on the worksheet, Private Sub Worksheet_Change(ByVal Target As Range) For X = 4 To 5 Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value MsgBox Target.Address Next End Sub As you will note you will go into a recursive loop. Hit CTRL+Break. There will be a way to do what you want but I doubt this is it. Post your code. Mike "Brad E." wrote: You've got it correct. I am just trying to set the value so that Worksheet_Change will be called with E16 the target and then again with F16 the target. For each of the vehicles 4 and 5 (because we are increasing from the old entry of "3"), I want to run further code which is already programmed in the Worksheet_Change event, with B16:J16 the intersect. -- Brad E. ----------------------------------------------- "Mike H" wrote: Hi, I think you need to post your code becuse this bit doesn't seem to make sense In a For loop (For X = 4 to 5, in this case), I have Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value It does nothing because for any value of X in the loop it becomes Range("A16").Offset(0, 4).Value = Range("A16").Offset(0, 4).Value in other words leave the value of that cell the same Mike "Brad E." wrote: Hi, In my Worksheet_Change event, I would like to change a cell and call the Worksheet_Change event again. In B3, the user enters a number up to 9, to access up to 9 vehicles. If the old value was 3 and the user enters 5, then the Worksheet_Change acknowledges the change and Displays some check boxes (Visible = True) for the 4th and 5th column. The check boxes were hidden when B3 originally went smaller than 5 (Visible = False). The ability to use these check boxes also depends on the entry in row 16. So first, I make the check boxes for vehicles 4 and 5 visible, and then want to re-enter the entry in row 16 for these 2 vehicles. I thought if I just set a cell to a value, it would initiate the Worksheet_Change event and the new Target would be the cell in row 16. In a For loop (For X = 4 to 5, in this case), I have Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value Part of my Worksheet_Change event includes an intersection of Target and "B16:J16", however the code after this intersection is not running. What are my options to be able to run the code? -- Thanks, Brad E. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Worksheet_Change call itself?
Here is the portion I am trying to get working for me. In the middle of the
50 code is the line to change some of B16:J16. When those cell(s) change, I want this same code to run with (E16) as the new target, and it would GoTo 70 to run code. After execution of the E16 target, it would come back to the middle of the 50 code with B3 still as the target....you know, calling itself and returning. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ActiveSheet.ClearCircles Application.ScreenUpdating = False 10 If Intersect(Target, Range("A1")) Is Nothing Then GoTo 20 20 If Intersect(Target, Range("C2")) Is Nothing Then GoTo 30 30 If Intersect(Target, Range("F2")) Is Nothing Then GoTo 40 40 If Intersect(Target, Range("A3")) Is Nothing And Intersect(Target, Range("E4")) Is Nothing Then GoTo 50 50 If Intersect(Target, Range("B3")) Is Nothing Then GoTo 60 OLDCOUNT = Range("C3").Value If (Target + 0) = OLDCOUNT Then GoTo 999 'LOCK UNUSED CELLS ActiveSheet.Range("B15:J31").Locked = False ActiveSheet.Range("B43:J57").Locked = False If (Target + 0) < 9 Then X = Mid("CDEFGHIJ", Target, 1) ActiveSheet.Range(X & "15:J31").Locked = True ActiveSheet.Range(X & "43:J57").Locked = True End If 'MAKE CHECK BOXES AVAILABLE IF NEW COUNT OLD COUNT If (Target + 0) OLDCOUNT Then For X = (OLDCOUNT * 3 + 1) To (Target * 3) ActiveSheet.Shapes(X).Visible = True Next X For X = (OLDCOUNT + 1) To Target 'Implements a Worksheet_Change event on Type. Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value Next X End If 'MAKE CHECK BOXES UNAVAILABLE IF NEW COUNT < OLD COUNT If (Target + 0) < OLDCOUNT Then For X = (Target * 3 + 1) To (OLDCOUNT * 3) ActiveSheet.Shapes(X).Visible = False Next X End If Range("C3").Value = Target + 0 GoTo 999 60 If Intersect(Target, Range("B15:J15")) Is Nothing Then GoTo 70 70 If Intersect(Target, Range("B16:J16")) Is Nothing Then GoTo 80 Target = UCase(Target) TU = (Target = "T" Or Target = "U") ATU = (Target = "A" Or TU) AMTU = (Target = "M" Or ATU) ACMTU = (Target = "C" Or AMTU) With Target If AMTU Or .Offset(-1, 0).Value < 1998 Then Range(.Offset(3, 0), ..Offset(4, 0)).Locked = True _ Else Range(.Offset(3, 0), ..Offset(4, 0)).Locked = False If ACMTU Then Range(.Offset(5, 0), .Offset(9, 0)).Locked = True For X = ((.Column * 3) - 5) To ((.Column * 3) - 3) ActiveSheet.Shapes(X).Visible = False Next X Else Range(.Offset(5, 0), .Offset(9, 0)).Locked = False For X = ((.Column * 3) - 5) To ((.Column * 3) - 3) ActiveSheet.Shapes(X).Visible = True Next X End If If ATU Or (.Offset(-1, 0).Value < 1976 And .Offset(2, 0).Value = 7) Or _ (.Offset(-1, 0).Value 1989 And .Offset(-1, 0).Value < 2011 And .Offset(2, 0).Value = 27) Or _ (.Offset(-1, 0).Value 2010 And .Offset(2, 0).Value = 98) Then .Offset(10, 0).Locked = False Else .Offset(10, 0).Locked = True End If If TU Then Range(.Offset(11, 0), .Offset(14, 0)).Locked = True Else Range(.Offset(11, 0), .Offset(14, 0)).Locked = False End If End With GoTo 999 999 Application.ScreenUpdating = True ActiveSheet.CircleInvalid Application.EnableEvents = True End Sub -- Brad E. ---------------------------------------------- "Mike H" wrote: Brad, You'l l see what's wrong with that approach if you run this snippet of code by changing a cell on the worksheet, Private Sub Worksheet_Change(ByVal Target As Range) For X = 4 To 5 Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value MsgBox Target.Address Next End Sub As you will note you will go into a recursive loop. Hit CTRL+Break. There will be a way to do what you want but I doubt this is it. Post your code. Mike "Brad E." wrote: You've got it correct. I am just trying to set the value so that Worksheet_Change will be called with E16 the target and then again with F16 the target. For each of the vehicles 4 and 5 (because we are increasing from the old entry of "3"), I want to run further code which is already programmed in the Worksheet_Change event, with B16:J16 the intersect. -- Brad E. ----------------------------------------------- "Mike H" wrote: Hi, I think you need to post your code becuse this bit doesn't seem to make sense In a For loop (For X = 4 to 5, in this case), I have Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value It does nothing because for any value of X in the loop it becomes Range("A16").Offset(0, 4).Value = Range("A16").Offset(0, 4).Value in other words leave the value of that cell the same Mike "Brad E." wrote: Hi, In my Worksheet_Change event, I would like to change a cell and call the Worksheet_Change event again. In B3, the user enters a number up to 9, to access up to 9 vehicles. If the old value was 3 and the user enters 5, then the Worksheet_Change acknowledges the change and Displays some check boxes (Visible = True) for the 4th and 5th column. The check boxes were hidden when B3 originally went smaller than 5 (Visible = False). The ability to use these check boxes also depends on the entry in row 16. So first, I make the check boxes for vehicles 4 and 5 visible, and then want to re-enter the entry in row 16 for these 2 vehicles. I thought if I just set a cell to a value, it would initiate the Worksheet_Change event and the new Target would be the cell in row 16. In a For loop (For X = 4 to 5, in this case), I have Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value Part of my Worksheet_Change event includes an intersection of Target and "B16:J16", however the code after this intersection is not running. What are my options to be able to run the code? -- Thanks, Brad E. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Worksheet_Change call itself?
Using Line Numbers is definitely "old school" stuff ie Excel 95. Most of it is rubbish. Goto 999 999 ...... Activesheet.CircleInvalid What? ................. In fact the whole thing is rubbish and poorly constructed. You should implement the "If Then End if" construct. And maybe "Select Case". I am surprised you even got it to work at all. To get a better response, you would better to explain the whole construct because reading the code is just bullsh*t. Break the query up into four questions to get the best results. No one is going to rewrite this for free. The original question was the best. Brad E." wrote: Here is the portion I am trying to get working for me. In the middle of the 50 code is the line to change some of B16:J16. When those cell(s) change, I want this same code to run with (E16) as the new target, and it would GoTo 70 to run code. After execution of the E16 target, it would come back to the middle of the 50 code with B3 still as the target....you know, calling itself and returning. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ActiveSheet.ClearCircles Application.ScreenUpdating = False 10 If Intersect(Target, Range("A1")) Is Nothing Then GoTo 20 20 If Intersect(Target, Range("C2")) Is Nothing Then GoTo 30 30 if Intersect(Target, Range("F2")) Is Nothing Then GoTo 40 40 If Intersect(Target, Range("A3")) Is Nothing And Intersect(Target, Range("E4")) Is Nothing Then GoTo 50 50 If Intersect(Target, Range("B3")) Is Nothing Then GoTo 60 OLDCOUNT = Range("C3").Value If (Target + 0) = OLDCOUNT Then GoTo 999 'LOCK UNUSED CELLS ActiveSheet.Range("B15:J31").Locked = False ActiveSheet.Range("B43:J57").Locked = False If (Target + 0) < 9 Then X = Mid("CDEFGHIJ", Target, 1) ActiveSheet.Range(X & "15:J31").Locked = True ActiveSheet.Range(X & "43:J57").Locked = True End If 'MAKE CHECK BOXES AVAILABLE IF NEW COUNT OLD COUNT If (Target + 0) OLDCOUNT Then For X = (OLDCOUNT * 3 + 1) To (Target * 3) ActiveSheet.Shapes(X).Visible = True Next X For X = (OLDCOUNT + 1) To Target 'Implements a Worksheet_Change event on Type. Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value Next X End If 'MAKE CHECK BOXES UNAVAILABLE IF NEW COUNT < OLD COUNT If (Target + 0) < OLDCOUNT Then For X = (Target * 3 + 1) To (OLDCOUNT * 3) ActiveSheet.Shapes(X).Visible = False Next X End If Range("C3").Value = Target + 0 GoTo 999 60 If Intersect(Target, Range("B15:J15")) Is Nothing Then GoTo 70 70 If Intersect(Target, Range("B16:J16")) Is Nothing Then GoTo 80 Target = UCase(Target) TU = (Target = "T" Or Target = "U") ATU = (Target = "A" Or TU) AMTU = (Target = "M" Or ATU) ACMTU = (Target = "C" Or AMTU) With Target If AMTU Or .Offset(-1, 0).Value < 1998 Then Range(.Offset(3, 0), .Offset(4, 0)).Locked = True _ Else Range(.Offset(3, 0), .Offset(4, 0)).Locked = False If ACMTU Then Range(.Offset(5, 0), .Offset(9, 0)).Locked = True For X = ((.Column * 3) - 5) To ((.Column * 3) - 3) ActiveSheet.Shapes(X).Visible = False Next X Else Range(.Offset(5, 0), .Offset(9, 0)).Locked = False For X = ((.Column * 3) - 5) To ((.Column * 3) - 3) ActiveSheet.Shapes(X).Visible = True Next X End If If ATU Or (.Offset(-1, 0).Value < 1976 And .Offset(2, 0).Value = 7) Or _ (.Offset(-1, 0).Value 1989 And .Offset(-1, 0).Value < 2011 And .Offset(2, 0).Value = 27) Or _ (.Offset(-1, 0).Value 2010 And .Offset(2, 0).Value = 98) Then .Offset(10, 0).Locked = False Else .Offset(10, 0).Locked = True End If If TU Then Range(.Offset(11, 0), .Offset(14, 0)).Locked = True Else Range(.Offset(11, 0), .Offset(14, 0)).Locked = False End If End With GoTo 999 999 Application.ScreenUpdating = True ActiveSheet.CircleInvalid Application.EnableEvents = True End Sub -- Brad E. ---------------------------------------------- "Mike H" wrote: Brad, You'l l see what's wrong with that approach if you run this snippet of code by changing a cell on the worksheet, Private Sub Worksheet_Change(ByVal Target As Range) For X = 4 To 5 Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value MsgBox Target.Address Next End Sub As you will note you will go into a recursive loop. Hit CTRL+Break. There will be a way to do what you want but I doubt this is it. Post your code. Mike "Brad E." wrote: You've got it correct. I am just trying to set the value so that Worksheet_Change will be called with E16 the target and then again with F16 the target. For each of the vehicles 4 and 5 (because we are increasing from the old entry of "3"), I want to run further code which is already programmed in the Worksheet_Change event, with B16:J16 the intersect. -- Brad E. ----------------------------------------------- "Mike H" wrote: Hi, I think you need to post your code becuse this bit doesn't seem to make sense In a For loop (For X = 4 to 5, in this case), I have Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value It does nothing because for any value of X in the loop it becomes Range("A16").Offset(0, 4).Value = Range("A16").Offset(0, 4).Value in other words leave the value of that cell the same Mike "Brad E." wrote: Hi, In my Worksheet_Change event, I would like to change a cell and call the Worksheet_Change event again. In B3, the user enters a number up to 9, to access up to 9 vehicles. If the old value was 3 and the user enters 5, then the Worksheet_Change acknowledges the change and Displays some check boxes (Visible = True) for the 4th and 5th column. The check boxes were hidden when B3 originally went smaller than 5 (Visible = False). The ability to use these check boxes also depends on the entry in row 16. So first, I make the check boxes for vehicles 4 and 5 visible, and then want to re-enter the entry in row 16 for these 2 vehicles. I thought if I just set a cell to a value, it would initiate the Worksheet_Change event and the new Target would be the cell in row 16. In a For loop (For X = 4 to 5, in this case), I have Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value Part of my Worksheet_Change event includes an intersection of Target and "B16:J16", however the code after this intersection is not running. What are my options to be able to run the code? -- Thanks, Brad E. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Worksheet_Change call itself?
It works if I don't disable the "EnableEvents" at the beginning of the
Worksheet_Change code. -- Brad E. "Brad E." wrote: Hi, In my Worksheet_Change event, I would like to change a cell and call the Worksheet_Change event again. In B3, the user enters a number up to 9, to access up to 9 vehicles. If the old value was 3 and the user enters 5, then the Worksheet_Change acknowledges the change and Displays some check boxes (Visible = True) for the 4th and 5th column. The check boxes were hidden when B3 originally went smaller than 5 (Visible = False). The ability to use these check boxes also depends on the entry in row 16. So first, I make the check boxes for vehicles 4 and 5 visible, and then want to re-enter the entry in row 16 for these 2 vehicles. I thought if I just set a cell to a value, it would initiate the Worksheet_Change event and the new Target would be the cell in row 16. In a For loop (For X = 4 to 5, in this case), I have Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value Part of my Worksheet_Change event includes an intersection of Target and "B16:J16", however the code after this intersection is not running. What are my options to be able to run the code? -- Thanks, Brad E. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Worksheet_Change call itself?
Actually, I will want to Re-enable events prior to changing the cell.
-- Brad E. "Brad E." wrote: It works if I don't disable the "EnableEvents" at the beginning of the Worksheet_Change code. -- Brad E. "Brad E." wrote: Hi, In my Worksheet_Change event, I would like to change a cell and call the Worksheet_Change event again. In B3, the user enters a number up to 9, to access up to 9 vehicles. If the old value was 3 and the user enters 5, then the Worksheet_Change acknowledges the change and Displays some check boxes (Visible = True) for the 4th and 5th column. The check boxes were hidden when B3 originally went smaller than 5 (Visible = False). The ability to use these check boxes also depends on the entry in row 16. So first, I make the check boxes for vehicles 4 and 5 visible, and then want to re-enter the entry in row 16 for these 2 vehicles. I thought if I just set a cell to a value, it would initiate the Worksheet_Change event and the new Target would be the cell in row 16. In a For loop (For X = 4 to 5, in this case), I have Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value Part of my Worksheet_Change event includes an intersection of Target and "B16:J16", however the code after this intersection is not running. What are my options to be able to run the code? -- Thanks, Brad E. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Worksheet_Change call itself?
Sorry about the diatribe in my first reply.
Why do you not just implement a userform. The useform could enter the data into the worksheet. If the selected quantity is not =<9 then you can hide the check boxes or renove the quantities. It will be easier to use and restrict the user frm inputting the wrong values in the wrong places. "Brad E." wrote: Actually, I will want to Re-enable events prior to changing the cell. -- Brad E. "Brad E." wrote: It works if I don't disable the "EnableEvents" at the beginning of the Worksheet_Change code. -- Brad E. "Brad E." wrote: Hi, In my Worksheet_Change event, I would like to change a cell and call the Worksheet_Change event again. In B3, the user enters a number up to 9, to access up to 9 vehicles. If the old value was 3 and the user enters 5, then the Worksheet_Change acknowledges the change and Displays some check boxes (Visible = True) for the 4th and 5th column. The check boxes were hidden when B3 originally went smaller than 5 (Visible = False). The ability to use these check boxes also depends on the entry in row 16. So first, I make the check boxes for vehicles 4 and 5 visible, and then want to re-enter the entry in row 16 for these 2 vehicles. I thought if I just set a cell to a value, it would initiate the Worksheet_Change event and the new Target would be the cell in row 16. In a For loop (For X = 4 to 5, in this case), I have Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value Part of my Worksheet_Change event includes an intersection of Target and "B16:J16", however the code after this intersection is not running. What are my options to be able to run the code? -- Thanks, Brad E. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Worksheet_Change call itself? | Excel Programming | |||
Can Worksheet_Change call itself? | Excel Programming | |||
Call sub from worksheet_change | Excel Programming | |||
Call Worksheet_Change macro in another worksheet | Excel Programming |