Need a macro to alert me when the value of a formula in another sheet changes
 

Hi,

I'm working on a complex spreadsheet with many sheets and need to
know
if something I do in any sheet causes a particular cell value in the
first sheet to change.


I've played around with onevent macros but they seem to be triggered
only if a cell changes content rather than changing value. ie. a cell
is deleted or overkeyed, rather than a formula just giving a
different
value.


Can anyone assist with the code I need to do this?


Many thanks and Merry Christmas to all.


Kind regards,


Ana

Need a macro to alert me when the value of a formula in another sheet changes
 

On Wed, 22 Dec 2010 21:08:15 -0800 (PST), Ana
wrote:

Hi,

I'm working on a complex spreadsheet with many sheets and need to
know
if something I do in any sheet causes a particular cell value in the
first sheet to change.


I've played around with onevent macros but they seem to be triggered
only if a cell changes content rather than changing value. ie. a cell
is deleted or overkeyed, rather than a formula just giving a
different
value.


Can anyone assist with the code I need to do this?



You need a combination of events. First, you put some code into a
Workbook Open event which saves the initial value of the cell you want
to monitor.

Then, in a workbook change event you compare the new value to the
stored value. If it changes, you can notify yourself with a message
box or by changing the status bar. Finally, you store the new value
in the variable which initially held the original value.

Here's an example: http://excel.bigresource.com/Track/excel-UvyDmjWJ/