Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
The problem is with the Case 61, 62.
The values in col 61 and 62 are results of stock prices downloaded from some external source of Excel. So I think that is the reason the Cells(Target.Row, 46) = Cells(Target.Row, 49) does not work. I can make manual changes on the sheet, where I over write the formula, and it works okay. I tried using Worksheet_Calculate() but could not make that work either. I was able to make a googled example work where you declare a range in a standard module, the calculate macro in the thisworkbook module and another macro in the sheet module. Trying to adapt that system to my Case 61, 62 is beyond me. Am I on the right track? Thanks. Howard Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AW:AW,BI48:BJ65")) Is Nothing _ Or Target.count 1 Then Exit Sub Dim rngAW As Range Dim LRow As Long Dim c As Range LRow = Cells(Rows.count, 49).End(xlUp).Row Set rngAW = Range("AW6:AW" & LRow) Select Case Target.Column Case 49 For Each c In rngAW If c.Offset(, -3) = "" Then If c 0 Then c.Offset(, -3) = c End If End If Next Case 61, 62 If Cells(Target.Row, 61) = 0 Or Cells(Target.Row, 62) = 0 Then Exit Sub Cells(Target.Row, 46) = Cells(Target.Row, 49) End Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
Hi Howard,
Am Sat, 18 Jan 2014 01:22:09 -0800 (PST) schrieb L. Howard: The problem is with the Case 61, 62. The values in col 61 and 62 are results of stock prices downloaded from some external source of Excel. try: Private Sub Worksheet_Calculate() Dim LRow As Long Dim rngC As Range LRow = Cells(Rows.Count, 61).End(xlUp).Row For Each rngC In Range("BI1:BI" & LRow) If rngC.Value * rngC.Offset(, 1).Value < 0 Then Cells(rngC.Row, 46) = Cells(rngC.Row, 49) End If Next End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AW:AW")) Is Nothing _ Or Target.Count 1 Then Exit Sub Dim rngAW As Range Dim LRow As Long Dim c As Range LRow = Cells(Rows.Count, 49).End(xlUp).Row Set rngAW = Range("AW6:AW" & LRow) For Each c In rngAW If c.Offset(, -3) = "" Then If c 0 Then c.Offset(, -3) = c End If End If Next End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
On Saturday, January 18, 2014 1:37:46 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Sat, 18 Jan 2014 01:22:09 -0800 (PST) schrieb L. Howard: The problem is with the Case 61, 62. The values in col 61 and 62 are results of stock prices downloaded from some external source of Excel. try: Private Sub Worksheet_Calculate() Dim LRow As Long Dim rngC As Range LRow = Cells(Rows.Count, 61).End(xlUp).Row For Each rngC In Range("BI1:BI" & LRow) If rngC.Value * rngC.Offset(, 1).Value < 0 Then Cells(rngC.Row, 46) = Cells(rngC.Row, 49) End If Next End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AW:AW")) Is Nothing _ Or Target.Count 1 Then Exit Sub Dim rngAW As Range Dim LRow As Long Dim c As Range LRow = Cells(Rows.Count, 49).End(xlUp).Row Set rngAW = Range("AW6:AW" & LRow) For Each c In rngAW If c.Offset(, -3) = "" Then If c 0 Then c.Offset(, -3) = c End If End If Next End Sub Regards Claus B. Thanks Claus. I am assuming it will take a real life stock price up date to properly test the code. If I do any manual entries it makes the proper copies to column 46 from column 49 but then goes into a continuous loop. Probably won't know until Monday. (Markets closed) I trust the code much more than the incredibly complex worksheet I'm using in on. Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
Hi Howard,
Am Sat, 18 Jan 2014 03:48:48 -0800 (PST) schrieb L. Howard: If I do any manual entries it makes the proper copies to column 46 from column 49 but then goes into a continuous loop. set into the Worksheet_Change event Application.EnableEvents = False and at the end to TRUE Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
On Saturday, January 18, 2014 4:03:28 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Sat, 18 Jan 2014 03:48:48 -0800 (PST) schrieb L. Howard: If I do any manual entries it makes the proper copies to column 46 from column 49 but then goes into a continuous loop. set into the Worksheet_Change event Application.EnableEvents = False and at the end to TRUE Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Did as you say, but the loop continues. However, the code sure does work as far as I can see. Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
Did as you say, but the loop continues. However, the code sure does work as far as I can see. Howard Forgot to say, just learned there is a button that brings some other values into play that essentially makes the Calculate code fire. I'll look for it and maybe do the enable events False/True on that code also. Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
On Saturday, January 18, 2014 4:23:32 AM UTC-8, L. Howard wrote:
Did as you say, but the loop continues. However, the code sure does work as far as I can see. Howard Forgot to say, just learned there is a button that brings some other values into play that essentially makes the Calculate code fire. I'll look for it and maybe do the enable events False/True on that code also. Howard I put the events False/True on that code and no continuous loop on the calculate code but now it ignores the 0 values and copies all cells. Howard |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
On Saturday, January 18, 2014 4:37:57 AM UTC-8, L. Howard wrote:
On Saturday, January 18, 2014 4:23:32 AM UTC-8, L. Howard wrote: Did as you say, but the loop continues. However, the code sure does work as far as I can see. Howard Forgot to say, just learned there is a button that brings some other values into play that essentially makes the Calculate code fire. I'll look for it and maybe do the enable events False/True on that code also. Howard I put the events False/True on that code and no continuous loop on the calculate code but now it ignores the 0 values and copies all cells. Howard If I run it like this from a forms button it gets the job done. Sub SameAsCalc() 'Private Sub Worksheet_Calculate() Dim LRow As Long Dim rngC As Range LRow = Cells(Rows.count, 61).End(xlUp).Row For Each rngC In Range("BI6:BI" & LRow) If rngC.Value * rngC.Offset(, 1).Value < 0 Then Cells(rngC.Row, 46) = Cells(rngC.Row, 49) End If Next End Sub Howard |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
Hi Howard,
Am Sat, 18 Jan 2014 04:37:57 -0800 (PST) schrieb L. Howard: I put the events False/True on that code and no continuous loop on the calculate code but now it ignores the 0 values and copies all cells. for me it works fine and the 0 values are not ignored. Could it be that your values are not really 0 but shown as 0 because cell format? Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
On Saturday, January 18, 2014 6:07:45 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Sat, 18 Jan 2014 04:37:57 -0800 (PST) schrieb L. Howard: I put the events False/True on that code and no continuous loop on the calculate code but now it ignores the 0 values and copies all cells. for me it works fine and the 0 values are not ignored. Could it be that your values are not really 0 but shown as 0 because cell format? Regards Claus B. I put the code back under Private Sub Worksheet_Calculate(). I have tried formatting as Accounting, General, Currency, Number and it goes into the loop for each format until I hit Esc. On a forms button, code works okay on all four of those formats. I provide a "Calculation" by having one each of the 61, 62 cells refer to a cell on the sheet. (=BJ1, it's out of the code range) then I change the value of BJ1. I get the loop for all the formats mentioned and also a couple of Excel crashes. I'm thinking there is just improper coding practices within this pretty complex worksheet. I have been providing an OP with several answers to "I want this or that" and what I suggest works most of the time. When it doesn't I offer another until "it works". We are nine pages in the thread and this problem and one other is where I came here for help. I'm sorta grasping at straws to provide meaningful input. Howard |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
Hi Howard,
Am Sat, 18 Jan 2014 10:45:12 -0800 (PST) schrieb L. Howard: I have tried formatting as Accounting, General, Currency, Number and it goes into the loop for each format until I hit Esc. in my test workbook I only have the Worksheet_Calculate and the Worksheet_Change code. Both works fine and I get no loop. The loop nust be fired from the other code into your workbook. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event not responding to change made by formula.
On Saturday, January 18, 2014 10:51:18 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Sat, 18 Jan 2014 10:45:12 -0800 (PST) schrieb L. Howard: I have tried formatting as Accounting, General, Currency, Number and it goes into the loop for each format until I hit Esc. in my test workbook I only have the Worksheet_Calculate and the Worksheet_Change code. Both works fine and I get no loop. The loop nust be fired from the other code into your workbook. Regards Claus B. That confirms my suspicions. I'll go forward with that and see what I can do. Having your code solutions that I know I can trust will be a help. Thanks for your time, and advice. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No response to change_event | Excel Programming | |||
Start a macro from a change in a cell value made by a formula? | Excel Programming | |||
Save and print if change made | Excel Programming | |||
Combobox Change_Event | Excel Programming | |||
Change_Event | Excel Programming |