change event for an external link

vvershas · Apr 7th, 2002, 05:43 AM

hello,
I need to copy and paste a range of data whenever a particular cell's value changes. this particular cell is updated by an external link. I've played around with the change and the calculate event. The change event will only work if I update the cell and click out of it.
I also need this range of data to go to the next column each time the cell is updated so that i can compare changes since the previous update.
any help would be very appreciated

Tom Schreiner · Apr 7th, 2002, 06:46 AM

Hi
You can accomplish this via the calculate event. I wrote a trade tracking program for several people which tracks prices and volume changed and automatically records changes on the next row/column. If you'd like, I'll send it to you. Is an Excel spreadsheet. Maybe the source code can help you out a bit.
Tom

vvershas · Apr 7th, 2002, 07:50 AM

Hi Tom,
Thanks for the info. Your spreadsheet sounds ideal so please do send it. The spreadsheet I'm working on analyses tote betting from a terminal session so it's very similar to stock analysis.

Cheers
Shaun

Tom Schreiner · Apr 7th, 2002, 08:27 AM

I'll need an E-Mail address. If you do not want to post it here, then E-Mail me
TsTom@hotmail.com
Tom

vvershas · Apr 7th, 2002, 06:58 PM

thanks Tom
the calculate event seems to be for in the event of any cell in the sheet recalculating. how can I set it so that it is in the event of only 1 particular cell recalculating?

cheers

Tom Schreiner · Apr 7th, 2002, 07:16 PM

Hi
I suppose you may have to work around this by reserving one sheet to be the sole reciever of your query updates. I'm not sure exactly what your are doing so it is quite difficult for me to understand how I might help you. The sheet I sent you recieves updates for four items. Time, Date, Price, and volume. I attempted to capture the changes for Price and volume only.
You may need to store a previous value and then compare it to the new value, if any, to detect a change in the cells in which your query are concerned.
I believe that is how I did it.
If you really get stuck, send me a sample stream of your data and I will try and figure it out. As you can see by my code, I am no expert, but it does work for the purpose that I created it for. At least that's what I'm told.
Tom

[ This Message was edited by: TsTom on 2002-04-07 18:18 ]

vvershas · Apr 7th, 2002, 07:17 PM

Tom,

I'll re phrase that last question,
all examples i have seen are in the case of a cell having a particular value.
I need to invoke a sub if the cell changes to any new value. (in my case, an update in the win pool on the tote)

cheers

Tom Schreiner · Apr 7th, 2002, 07:21 PM

I edited my former reply.
The workbook I sent you captures the link change via the calculation event. Because I know of no way to determine which cells provoked the event, I store the previous value from the link, compare it to the new, if any, and then run my code from there.
So it does not really matter which cell caused the event to fire off. It only matters that the values we are concerned with have changed or not.
Do you understand?
Tom

Tom Schreiner · Apr 7th, 2002, 07:24 PM

The workbook I sent you is based soley upon changing values. If a price or volume changes then the code sends the new values, along with the date and time, to a new line/row. This seems to be what you are after?

Tom Schreiner · Apr 7th, 2002, 07:25 PM

I have to go.
Please read the above and If you'd like, send me some sample data from your stream.
Thanks
Tom
TsTom@hotmail.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 7th, 2002, 05:43 AM	#1
vvershas New Member Join Date: Apr 2002 Location: Australia Posts: 20	hello, I need to copy and paste a range of data whenever a particular cell's value changes. this particular cell is updated by an external link. I've played around with the change and the calculate event. The change event will only work if I update the cell and click out of it. I also need this range of data to go to the next column each time the cell is updated so that i can compare changes since the previous update. any help would be very appreciated

Apr 7th, 2002, 06:46 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi You can accomplish this via the calculate event. I wrote a trade tracking program for several people which tracks prices and volume changed and automatically records changes on the next row/column. If you'd like, I'll send it to you. Is an Excel spreadsheet. Maybe the source code can help you out a bit. Tom

Apr 7th, 2002, 07:50 AM	#3
vvershas New Member Join Date: Apr 2002 Location: Australia Posts: 20	Hi Tom, Thanks for the info. Your spreadsheet sounds ideal so please do send it. The spreadsheet I'm working on analyses tote betting from a terminal session so it's very similar to stock analysis. Cheers Shaun

Apr 7th, 2002, 08:27 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I'll need an E-Mail address. If you do not want to post it here, then E-Mail me TsTom@hotmail.com Tom

Apr 7th, 2002, 06:58 PM	#5
vvershas New Member Join Date: Apr 2002 Location: Australia Posts: 20	thanks Tom the calculate event seems to be for in the event of any cell in the sheet recalculating. how can I set it so that it is in the event of only 1 particular cell recalculating? cheers

Apr 7th, 2002, 07:16 PM	#6
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi I suppose you may have to work around this by reserving one sheet to be the sole reciever of your query updates. I'm not sure exactly what your are doing so it is quite difficult for me to understand how I might help you. The sheet I sent you recieves updates for four items. Time, Date, Price, and volume. I attempted to capture the changes for Price and volume only. You may need to store a previous value and then compare it to the new value, if any, to detect a change in the cells in which your query are concerned. I believe that is how I did it. If you really get stuck, send me a sample stream of your data and I will try and figure it out. As you can see by my code, I am no expert, but it does work for the purpose that I created it for. At least that's what I'm told. Tom [ This Message was edited by: TsTom on 2002-04-07 18:18 ]

Apr 7th, 2002, 07:17 PM	#7
vvershas New Member Join Date: Apr 2002 Location: Australia Posts: 20	Tom, I'll re phrase that last question, all examples i have seen are in the case of a cell having a particular value. I need to invoke a sub if the cell changes to any new value. (in my case, an update in the win pool on the tote) cheers

Apr 7th, 2002, 07:21 PM	#8
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I edited my former reply. The workbook I sent you captures the link change via the calculation event. Because I know of no way to determine which cells provoked the event, I store the previous value from the link, compare it to the new, if any, and then run my code from there. So it does not really matter which cell caused the event to fire off. It only matters that the values we are concerned with have changed or not. Do you understand? Tom

Apr 7th, 2002, 07:24 PM	#9
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	The workbook I sent you is based soley upon changing values. If a price or volume changes then the code sends the new values, along with the date and time, to a new line/row. This seems to be what you are after?

Apr 7th, 2002, 07:25 PM	#10
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I have to go. Please read the above and If you'd like, send me some sample data from your stream. Thanks Tom TsTom@hotmail.com