首页 文章

组合多个Worksheet_Change宏

提问于
浏览 次
0

我正在尝试组合多个worksheet_change宏（请参阅下面的代码） . 我的目标是每当“目标”范围（合并的下拉列表单元格）发生变化时，下面的范围（再次，合并的单元格）将清除 . 我需要在更改MULTIPLE不同单元格时执行此操作，因此多个工作表更改代码 .

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
excel vba excel-vba

2 回答

  • 1

    下面的代码只是你的代码放在1 Sub 中，多个 If statements . 唯一的变化是 If 现在是 If Not ，如果有 Intersect 然后是 Exit sub ，它将处理代码 .

    以下代码将起到作用：

    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
    回复于
  • 2
    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
    回复于

相关问题