CODEWITHSUNDEEP
vbaexceleventsevent-handling
Richard Sto
Richard Sto

Reputation: 31

Running a different macro for a different cell only when changed manually

My problem is that the macros I wrote change the values of the cells triggering again a macro to change one of the other cells.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target
        If Not Intersect(cell, Range("c2")) Is Nothing Then
            Macro1
        ElseIf Not Intersect(cell, Range("C3")) Is Nothing Then
            Macro2
        ElseIf Not Intersect(cell, Range("d8")) Is Nothing Then
            Macro3
        End If
    Next cell
End Sub

The macros running always change the other cells, what makes it a endless loop at the moment.

Is there a way to only make manual input/ change of the cell let the macro run?

Upvotes: 0

Views: 79

Answers (1)

R3uK
R3uK

Reputation: 14537

Two solutions for this :

  1. Add Application.EnableEvents = False at the start of your _change event and set it to True at the end
  2. Create a Public Boolean to test if you are already doing any update automatically

Something like this (solution 2) :

Public DisableEvents As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If DisableEvents Then Exit Sub
    DisableEvents = True

    Dim cell As Range
    For Each cell In Target
        If Not Intersect(cell, Range("c2")) Is Nothing Then
            Macro1
        ElseIf Not Intersect(cell, Range("C3")) Is Nothing Then
            Macro2
        ElseIf Not Intersect(cell, Range("d8")) Is Nothing Then
            Macro3
        End If
    Next cell

    DisableEvents = False
End Sub


Sub Macro1()
If DisableEvents Then Exit Sub

'Rest of your code

End Sub

Upvotes: 2

Related Questions