Triggering a Worksheet Change event

sb fredster · Mar 11th, 2002, 05:09 PM

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!)

Juan Pablo González · Mar 11th, 2002, 05:19 PM

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

dk · Mar 11th, 2002, 05:21 PM

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

sb fredster · Mar 12th, 2002, 09:35 AM

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

Mar 11th, 2002, 05:19 PM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	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

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

Mar 11th, 2002, 05:09 PM	#1
sb fredster New Member Join Date: Mar 2002 Posts: 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!)

Mar 11th, 2002, 05:21 PM	#3
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	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

Mar 12th, 2002, 09:35 AM	#4
sb fredster New Member Join Date: Mar 2002 Posts: 3	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