CODEWITHSUNDEEP
vbaexcel
user5317164
user5317164

Reputation:

Combining multiple Worksheet_Change macros

I am trying to combine multiple worksheet_change macros (see code below). My goal is that whenever the "target" range (a merged, drop-down list cell) is changed, the ranges below (again, merged cells) will clear. I need to do this for when MULTIPLE different cells are changed, hence the multiple worksheet change codes.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("J1:O1")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        Range("J2:O3").ClearContents
        Range("D15:E15").ClearContents
            Range("B16:E16").ClearContents
                Range("B17:E19").ClearContents
        Range("D20:E20").ClearContents
            Range("B21:E21").ClearContents
                Range("B22:E24").ClearContents
        Range("D25:E25").ClearContents
            Range("B26:E26").ClearContents
                 Range("B27:E29").ClearContents
        Range("D30:E30").ClearContents
            Range("B31:E31").ClearContents
                 Range("B32:E34").ClearContents
        Range("B3:H14").ClearContents
    Application.EnableEvents = True
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("J2:K2")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        Range("J3:K3").ClearContents
    Application.EnableEvents = True
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("L2:M2")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        Range("L3:M3").ClearContents
    Application.EnableEvents = True
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("N2:O2")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        Range("N3:O3").ClearContents
    Application.EnableEvents = True
    End Sub

Upvotes: 0

Views: 1895

Answers (2)

DragonSamu
DragonSamu

Reputation: 1163

The code below is simply your code put together in 1 Sub with multiple If statements. The only change is that the If is now an If Not which will process the code if there is an Intersect and then Exit sub.

The following code will do the trick:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("J1:O1")) Is Nothing Then
        Application.EnableEvents = False
        Range("J2:O3").ClearContents
        Range("D15:E15").ClearContents
        Range("B16:E16").ClearContents
        Range("B17:E19").ClearContents
        Range("D20:E20").ClearContents
        Range("B21:E21").ClearContents
        Range("B22:E24").ClearContents
        Range("D25:E25").ClearContents
        Range("B26:E26").ClearContents
        Range("B27:E29").ClearContents
        Range("D30:E30").ClearContents
        Range("B31:E31").ClearContents
        Range("B32:E34").ClearContents
        Range("B3:H14").ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
    If Not Intersect(Target, Range("J2:K2")) Is Nothing Then
        Application.EnableEvents = False
        Range("J3:K3").ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
    If Not Intersect(Target, Range("L2:M2")) Is Nothing Then
        Application.EnableEvents = False
        Range("L3:M3").ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
    If Not Intersect(Target, Range("N2:O2")) Is Nothing Then
        Application.EnableEvents = False
        Range("N3:O3").ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
End Sub

Upvotes: 2

user3819867
user3819867

Reputation: 1118

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J1:O1")) Is Nothing Then
    Application.EnableEvents = False
        Range("J2:O3").ClearContents
        Range("D15:E15").ClearContents
            Range("B16:E16").ClearContents
                Range("B17:E19").ClearContents
        Range("D20:E20").ClearContents
            Range("B21:E21").ClearContents
                Range("B22:E24").ClearContents
        Range("D25:E25").ClearContents
            Range("B26:E26").ClearContents
                 Range("B27:E29").ClearContents
        Range("D30:E30").ClearContents
            Range("B31:E31").ClearContents
                 Range("B32:E34").ClearContents
        Range("B3:H14").ClearContents
    Application.EnableEvents = True
End If

If Not Intersect(Target, Range("J2:K2")) Is Nothing Then
    Application.EnableEvents = False
        Range("J3:K3").ClearContents
    Application.EnableEvents = True
End If

If Not Intersect(Target, Range("L2:M2")) Is Nothing Then
    Application.EnableEvents = False
        Range("L3:M3").ClearContents
    Application.EnableEvents = True
End If

If Not Intersect(Target, Range("N2:O2")) Is Nothing Then
    Application.EnableEvents = False
        Range("N3:O3").ClearContents
    Application.EnableEvents = True
End If

End Sub

Upvotes: 1

Related Questions