Run macro if cell value changes

[Brettjg]

Hello there. I have the following code in a worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C63")) Is Nothing Then
Application.Run "Calculate_Stamp_Duty_NEW_HOME"
End If

If Not Intersect(Target, Range("D9")) Is Nothing Then
Application.Run "Calculate_Stamp_Duty_PROPERTY_3"
End If

End Sub

The first "IF" statement works perfectly because cell C63 is a cell that I
manually type a new value into. The second "IF" doesn't work. The cell D9
gets it's value automatically from another workbook. What I want to happen is
for the macro "Calculate_Stamp_Duty_PROPERTY_3" to run automatically
when the value in the other workbook is changed (manually, and therefore the
value of D9 is updated automatically in this workbook).

Can someone give me some advice please?
Regards, Brett

 

[Dave Peterson]

There's no Target passed to the worksheet_calculate() event.

You can have it run each time that that worksheet recalculates:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Application.Run "Calculate_Stamp_Duty_PROPERTY_3"
Application.EnableEvents = True
End Sub

Or you could try to keep track of the previous value:

Private Sub Worksheet_Calculate()
Static OldValD9 as Variant

if me.range("D9").value = OldVald9 then
'do nothing, it didn't change
else
oldvald9 = me.range("d9").value
'then go off and do what you want
Application.EnableEvents = False
Application.Run "Calculate_Stamp_Duty_PROPERTY_3"
Application.EnableEvents = True
end if
End Sub

 

[Brettjg]

Hi Dave, thanks for that. I must be getting better at this because in the
interim I came to the same conclusion myself and got it working. Your second
suggestion looks more useful though, because it will only do a calc when
necessary (right now it does a calc if I so much as look at the screen).
Thanks a lot, regards, Brett.