VBA - Worksheet_Calculate event triggers over and over again

Question

I am attempting to update a column (E8:E508) with the contents of another reference column (G8:G508) each time the reference column changes, using the following code:

Private Sub Worksheet_Calculate() Dim Rng As Range Set Rng = Range("G8:G503") If Not Intersect(Rng, Range("G8:G503")) Is Nothing Then Range("E8:E503") = Range("G8:G503").Value End If End Sub

The code works as intended, but appears to be running over and over again and eventually crashes Excel.

it makes no sense that you are using this command Intersect(Rng, Rng) .... what are you trying to do? — jsotola
– jsotola, Commented Oct 17, 2017 at 5:21
Understandable, I'm new to VBA. I am using that function to limit the Worksheet_Calculate event to that range - i.e. only a change to a cell within that range will trigger the event. — S. Lee
– S. Lee, Commented Oct 17, 2017 at 5:22
i think that you want the worksheet_change event, not the worksheet_calculate event — jsotola
– jsotola, Commented Oct 17, 2017 at 5:22
The reference column (G8:G503) is a column of formulas, rather than manual values - hence the Calculate function (Change didn't work). — S. Lee
– S. Lee, Commented Oct 17, 2017 at 5:23
Range("E8:E503") = Range("G8:G503").Value triggers another calculation, etc, etc. You need Application.EnableEvents = False before doing that (and set back to True after) — Tim Williams
– Tim Williams, Commented Oct 17, 2017 at 5:34

Tim Williams · Accepted Answer · 2017-10-17 06:09:49Z

Range("E8:E503") = Range("G8:G503").Value

triggers another calculation, which triggers your event handler, etc etc.

To prevent that endless cycle you need to temporarily disable events before doing that (and then re-enable after)

Private Sub Worksheet_Calculate() Application.EnableEvents = False Range("E8:E503").Value = Range("G8:G503").Value Application.EnableEvents = True End Sub

jsotola · Accepted Answer · 2017-10-17 05:35:28Z

try this

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim Rng As Range Set Rng = Range("G8:G503") If Not Intersect(Rng, Target) Is Nothing Then Range("E8:E503") = Rng.Value End If Application.EnableEvents = True End Sub

oh, my bad ... sorry ... you want to update when formula result changes, not when data is entered
why use VBA code? put =G8 into cell E8 then copy and paste all way down to E503

Collectives™ on Stack Overflow

VBA - Worksheet_Calculate event triggers over and over again

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related