Excel - Macro repeats on Worksheet Change

Closed
ja6924 - 21 Apr 2010 à 12:06
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 21 Apr 2010 à 12:14
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?

Related:

1 response

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
21 Apr 2010 à 12:14
Add at the start of the code

Application.EnableEvents = False



and at the end of the code

Application.EnableEvents = true