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

[Ana]

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

 

[dranon]

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/