Triggering a Worksheet Change event

sb fredster

New Member
Joined
Mar 10, 2002
Messages
3
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!)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top