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

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 wrote:

Well, we are getting there. With the following code the macro does get
triggered when I change the other workbook, but it gets a run time error of
"Object required (Error 424)"

Private Sub Worksheet_Calculate()
If Not Intersect(Target, Range("D9")) Is Nothing Then
Application.EnableEvents = False
Application.Run "Calculate_Stamp_Duty_PROPERTY_3"
Application.EnableEvents = True
End If
End Sub

Not being completely lazy, I did check the Help, but I don't understand it
(as I don't with justabout all VB help!). This is where I'm a bit dopey, you
see. I didn't even realise you could have two Private Subs in the one
worksheet. The other question is: I need to actually have 4 of these macros
in the sheet. I tried
Private Sub Worksheet_Calculate_1()

Private Sub Worksheet_Calculate_2()
etc but obviously the name of the PS is important because they didn't
trigger any action.

Thanks for your help so far, I think we've (you've?) nearly done it.
Regards Brett.

--

Dave Peterson

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.

"Dave Peterson" wrote:

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 wrote:

Well, we are getting there. With the following code the macro does get
triggered when I change the other workbook, but it gets a run time error of
"Object required (Error 424)"

Private Sub Worksheet_Calculate()
If Not Intersect(Target, Range("D9")) Is Nothing Then
Application.EnableEvents = False
Application.Run "Calculate_Stamp_Duty_PROPERTY_3"
Application.EnableEvents = True
End If
End Sub

Not being completely lazy, I did check the Help, but I don't understand it
(as I don't with justabout all VB help!). This is where I'm a bit dopey, you
see. I didn't even realise you could have two Private Subs in the one
worksheet. The other question is: I need to actually have 4 of these macros
in the sheet. I tried
Private Sub Worksheet_Calculate_1()

Private Sub Worksheet_Calculate_2()
etc but obviously the name of the PS is important because they didn't
trigger any action.

Thanks for your help so far, I think we've (you've?) nearly done it.
Regards Brett.

--

Dave Peterson