CODEWITHSUNDEEP
excelvba
P.b
P.b

Reputation: 11448

Run macro on change within target range | Change (ByVal Target As Range)

I'm working with the Worksheet_Change(ByVal Target As Range) event.

I wanted to run a macro if either on of cells A1 or A2 are changed. So the target range is set to [A1, A2]

Then, if A1 is changed, I want to clear A2, or the other way around (A2 is changed: clear A1).

Now the problem:

If A1 value is changed. the macro clears A2. That's seen as a change in A2, so it clears A1, which is a change, etc...

I'm sure it's something simple, but I can't see how I can have cell A1 be changed without triggering the change macro if A2 is being cleared.

Anyone who has experience in this?

Upvotes: 0

Views: 770

Answers (2)

chris neilsen
chris neilsen

Reputation: 53137

There are a few edge cases here, around changing more than one cell eg via copy pasting a range (possibly include other cells too).

Here's a version thats more robust in those cases

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Target, Me.Range("A1:A2"))
    If rng Is Nothing Then Exit Sub

    On Error GoTo SafeExit
    Application.EnableEvents = False

    Select Case rng.Address(0, 0)
        ' If both changed, what now? Default to clearing A2
        Case "A1", "A1:A2"
            If Not IsEmpty(Me.Cells(1, 1)) Then
                Me.Cells(2, 1).Clear
            End If
        Case "A2"
            If Not IsEmpty(Me.Cells(2, 1)) Then
                Me.Cells(1, 1).Clear
            End If
    End Select

SafeExit:
    Application.EnableEvents = True
End Sub

Upvotes: 1

BigBen
BigBen

Reputation: 49998

You can accomplish this by toggling events off and on, like the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A1:A2")) Is Nothing Then Exit Sub

    On Error GoTo SafeExit
    Application.EnableEvents = False

    If Target.Address = "$A$1" Then
        Me.Range("A2").Clear
    ElseIf Target.Address = "$A$2" Then
        Me.Range("A1").Clear
    End If

SafeExit:
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions