Running a macro on cell value change

[Guest]

I have the following code in a worksheet. The first test works perfectly
because the cell C63 is changed by a manual entry. The second test returns a
run-time error "Object required (Error 424)". The cell D9 gets it's value
automatically from another workbook Sheet1 Cell A1 (sic). When I update cell
A1 it does trigger the macro to run run in the worksheet in question (because
the value of D9 has subsequently changed) but it results in the above error.
I actually want to include 4 tests like the second one but I note that if I
change the PrivSub name from Worksheet_Calculate to Worksheet_Calculate_1
then it doesn't trigger at all.

So my two questions are these:
1. What is wrong with the code in Worksheet_Calculate to give the error and
2. What can I call four of these private subs to make them run (or can I
include the four tests in the one Worksheet_Calculate PrivSub)

Thankyou for your time, Brett

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C63")) Is Nothing Then
Application.EnableEvents = False
Application.Run "Calculate_Stamp_Duty_NEW_HOME"
Application.EnableEvents = True
End If

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

 

[Guest]

Hi Mike. I figured it out. I had to take out "Intersect(Target," and the
second closing bracket. Now it works like a charm. Thankyou for your help.
Regards, Brett

 

[Dave Peterson]

Check your other thread.

I have the following code in a worksheet. The first test works perfectly
because the cell C63 is changed by a manual entry. The second test returns a
run-time error "Object required (Error 424)". The cell D9 gets it's value
automatically from another workbook Sheet1 Cell A1 (sic). When I update cell
A1 it does trigger the macro to run run in the worksheet in question (because
the value of D9 has subsequently changed) but it results in the above error.
I actually want to include 4 tests like the second one but I note that if I
change the PrivSub name from Worksheet_Calculate to Worksheet_Calculate_1
then it doesn't trigger at all.

So my two questions are these:
1. What is wrong with the code in Worksheet_Calculate to give the error and
2. What can I call four of these private subs to make them run (or can I
include the four tests in the one Worksheet_Calculate PrivSub)

Thankyou for your time, Brett

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C63")) Is Nothing Then
Application.EnableEvents = False
Application.Run "Calculate_Stamp_Duty_NEW_HOME"
Application.EnableEvents = True
End If

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