VBA Run macro on cell value change from formula

[vbaanalyst]

I want to fire a macro when the value in a cell changes i.e when the result in cell C1 changes

[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]=A1*B1[/TD]
[/TR]
</TBODY>[/TABLE]

private sub worksheet_change(byval target as range)

if target.address = [c1].address then
Msgbox([A1] & " times " & [b1] & " equals " & [C1])
end if

End sub

This macro fires when you click in C1 and change the formula or do something, but not when the result changes e.g. changing A1 to 6 does not activate the macro.

In this case it's relatively easy - just target cells A1 and B1. Unfortunately my sheet is pulling in realtime price data (from bloomberg if you must know) so A1 and B1 will change value without ever being activated and the usual Worksheet_change event doesn't occur. You would encounter the same problems if you were using volatile functions such as =Rand() or pulling in data from other workbooks where it becomes hard to track which cells should be the triggers; which makes me think there must be a solution out there somewhere.
I am therefore looking for a macro that will activate if the [c1].value changes even if no-one clicks in that cell.

In an ideal world this macro should work when people are away from their desks and look something like

Sub Worksheet_Realtime()

if trigger_cell.value > trigger_level
call send_alert_email()
end if

End Sub


My current workaround is to use application.ontime and set my time lag to ~1 minute but this seems much more intensive than it needs to be. It would start to slow down old machines if there were 3 or 4 background sheets firing macros every 15s or so - not to mention the interference with other macros! It seems like the sort of problem others would have come across before - but I have yet to find a solution in my websearching! (though the question has been asked here before: Automatically execute an excel macro on a cell changed by a real-time add-in - Stack Overflow)

David

 

[VoG]

Try like this

Private Sub Worksheet_Calculate()
Static oldval
If Range("C1").Value <> oldval Then
    oldval = Range("C1").Value
    '
    'rest of your code here
    '
End If
End Sub

 

[vbaanalyst]

That seems to work - doesn't seem to slow down much either even with several ifs checking different conditions!

I'll let you know how it fares with the bloomberg terminal - inactive workbooks may become my enemy...

 

[vbaanalyst]

That works perfectly - even with bloomberg running on a background sheet.
Thanks for the tip