Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: change event for an external link

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •