worksheet_calculate

[scotty01]

I copied this simple code below from this site recently, to learn more
about vba code. It is my understanding that using worksheet_calculate,
the code should execute automatically upon changes to the worksheet.
But when I change the value in A2, the value in D2 does not change
unless I go to the code in the VBA editor and click on run sub/
userform (or press the F5 key).

I am working in Sheet1, and have the code in Sheet1, so I don't
understand why the code does not execute automatically. Automatic
calculation is set to 'on' (tools/options). I have tried other
worksheet_calculate examples with the same result (no automatic
execution) and I have tried running the program from a different pc
(just to see if I may have inadvertently set some option). I have also
exited the spreadsheet and re-opened, in case enableevents had not
been reset to 'true'.

There is probably a simple explanation, but it's all new to me.

Thanks for any help.

Scott

Private Sub Worksheet_Calculate()
'Must disable events otherwise will run again
'when A2 is saved to D2
Application.EnableEvents = False


If Range("A2") <> Range("D2") Then 'Value has changed
Range("d2") = Range("A2") 'Resave new A2 value
End If


Application.EnableEvents = True
End Sub

 

[Jacob Skaria]

Worksheet_Calculate() event is fired when ever an existing cell formula is
calculated or re-calculated or any cell values which affect the calculation
changes.

Please write this code under Worksheet_Activate() event. Since your code is
looking at changes to 1 column it is better to write the code in
Worksheet_Change(ByVal Target As Range) event with a filter on the Column;

Private Sub Worksheet_Change(ByVal Target As Range)

'If the code needs to be executed only for any changes in col 2
If Target.Column = 2

'Write your code here

End If
End Sub


If this post helps click Yes

 

[scotty01]

Worksheet_Calculate() event is fired when ever an existing cell formula is
calculated or re-calculated or any cell values which affect the calculation
changes.

Please write this code under  Worksheet_Activate() event. Since your code is
looking at changes to 1 column it is better to write the code in
Worksheet_Change(ByVal Target As Range) event with a filter on the Column;

Private Sub Worksheet_Change(ByVal Target As Range)

'If the code needs to be executed only for any changes in col 2
If Target.Column = 2

'Write your code here

End If
End Sub

If this post helps click Yes

Jacob,
I changed things as you specified and it worked. Thanks for the
explanation and the prompt response!