Triggering a Worksheet Change event
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Triggering a Worksheet Change event

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am trying to use the worksheet_change event to trigger some VBA in a worksheet. I want to have the code execute when values in column B change. Column B is currently a lookup formula that gets values from a table. What I want to occur is the following: Table values are updated, then column B recalculates picking up the new values, as each cell in column B gets its new lookup value, an event is triggered and the cell reference is returned. I can then evaluate the returned value in my VBA code, evaluate the change, and then go on to the next value that changed in column B.

    By application relates to stock prices and monitors when certain floor or ceiling prices are hit for different stock holdings. The lookup table is used as an efficient way of getting stock prices where many positions exist for each price.

    THANKS in advance for any input from the gurus (you know who you are!)

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The Change event isn't triggered by "calculations". You could use a Change event when you change the raw data, that affects your VLOOKUP.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I understand your question correctly, you're trying to execute some VBA code when the value of a cell in column B changes, where column B consists of formulae. If this is the case then you need to use the Worksheet_Calculate event as any change in the result of a formula doesn't fire the Worksheet_Change event. The Calculate event doesn't provide any data about which cells will be changed (this would be impractical), but you should be able to use it to suit your needs.

    Regards,
    D

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks for the input. My solution is to use the calculate event as a trigger. I then use a For-Each-Next loop to evaluate all the cells in my data range in order to determine if they changed... or didn't change. I was hoping to use the worksheet_change event as it has the nice feature of returning the cell that changed.... unfortunately it appears that a cell that changes by any means other than a user's manual input is not considered a "worksheet_change" event. So I have a work-around that is less efficient (since I need to scan all values) but it works. Thanks

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
  •  

 

 
DMCA.com