Avoid recursion in VBA on cell change

Question

I need to do some operations on a cell after user input. Right now, I'm doing something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("H5")) Is Nothing Then MyMacro
End Sub

The problem is that MyMacro is also changing the cell, recursively invoking Worksheet_Change, leading to a stack overflow.

How can I avoid that and still be able to work on the cell from my macro?

@SiddharthRout I dont think that is the way you should use "This question already has an answer". Because [MSDN Help Center](https://msdn.microsoft.com/en-us/library/office/ff839775.aspx) also shows an example with `Application.EnableEvents`. Also do countless of other guides/tutorials. To link to an answer which is "general" and just covers up a case "somewhere" is not the correct way. The question was explicit asking for not recursively triggering the `Worksheet_Change` and a "marked as [dublicate]" should lead to an answer which also explicit answeres that. — Dirk Reichel, Sep 03 '16 at 07:14
See second point in the linked answer @DirkReichel. The 2nd point not only shows what to do, it also explains why do we do it.. Which is the same as OP's query — Siddharth Rout, Sep 03 '16 at 07:18
And that is the "somewhere" part. Your linked answer is correct AND well made. But it is still just a "guide" for the `Worksheet_Change`. A question "How can I avoid recursively invoking `Worksheet_Change`" should get an answer like "With turning off the events via `Application.EnableEvents = False` because....". A link to your "guide" is ok and it seems to fit. Just the [dublicate] is wrong for this question and your answer. I am pretty sure you get the point here ;) — Dirk Reichel, Sep 03 '16 at 07:29

score 1 · Accepted Answer · answered Sep 03 '16 at 05:45

1

Use this:

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

answered Sep 03 '16 at 05:45

Stupid_Intern

3,382
8
37
74

I known it's been forever since this answer, but can you explain why? – Max Jul 24 '20 at 20:58
Because it turns off application events until the subroutine is done. – BrainSlugs83 Oct 19 '20 at 21:56

Avoid recursion in VBA on cell change

1 Answers1