Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
No response to change_event Howard Excel Programming 4 October 26th 13 11:25 AM
Start a macro from a change in a cell value made by a formula? Geejay Excel Programming 7 March 17th 10 03:23 PM
Save and print if change made Kiba Excel Programming 1 January 6th 09 04:42 PM
Combobox Change_Event JimRWR Excel Programming 10 April 23rd 07 08:58 PM
Change_Event Hawk Excel Programming 4 October 16th 03 04:06 PM


All times are GMT +1. The time now is 04:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"