Excel - Macro repeats on Worksheet Change [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I have set up a worksheet change event that will run a specific macro when a number is put in a specific cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If [d1] = 1 Then
Call macro1
End If

If [d1] = 2 Then
Call macro2
End If

End Sub


macro1 deletes row 5&6
macro2 deletes row 7&8
When I put 1 in cell D1, excel runs macro1, just like it should, however it will not stop running. It repeatedly runs macro1 again & again & again until I get an error message. I assume because every time macro1 deletes a row in the worksheet it is a worksheet change, so it runs the macro again & again & again. How do I get it to run just once? or some other fix?

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Add at the start of the code

Application.EnableEvents = False



and at the end of the code

Application.EnableEvents = true

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!