多个VBA工作表更改事件

Question

我仍然是新的宏，并试图将两个变化事件成功地结合在一起，任何帮助都将不胜感激！

第一次变革活动：

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G7:G9")) Is Nothing Then
    Select Case Range("G7:G9")
        Case "Individual": Macro1
        Case "Company": Macro2
        Case "Trust": Macro3
        Case "": Macro4
      End Select
End If

End Sub

第二次变革活动：

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Range("G9")
        Case "Income Tax Return" Or "Financial Accounts" Or "": Macro5
        Case "FBT": Macro6
        Case "BAS/IAS": Macro7
        Case "Contractor Reporting" Or "Workers Compensations" Or "Payroll Tax" Or "STP / PAYGW": Macro7
    End Select
End If

End Sub

当列表显示所选术语时，称为隐藏和取消隐藏各自工作表的宏。

谢谢!

Answer 1

一种方法可以由两个嵌套的Select Case在一个For each中处理。

代码：

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim specialRange As Range
    Dim cell As Range

    Set specialRange = Me.Range("G7,G9")

    ' Exit if doesn't intersect (this could be just an If Not wrapping the For Each
    If Intersect(Target, specialRange) Is Nothing Then Exit Sub

    For Each cell In specialRange

        ' First handle the cell address
        Select Case cell.Address
        Case "$G$7"
            ' Then its value
            Select Case cell.Value
            Case "Individual"
                'Macro1
            Case "Company"
                'Macro2
            Case "Trust"
                'Macro3
            Case vbNullString
                'Macro4
            Case Else
                'DoSomething?
            End Select

        Case "$G$9"
            Select Case cell.Value
            Case "Income Tax Return", "Financial Accounts", vbNullString
                'Macro5
            Case "FBT"
                'Macro6
            Case "BAS/IAS"
                'Macro7
            ' As the next case is calling the same macro as the previous, your could merge them
            Case "Contractor Reporting", "Workers Compensations", "Payroll Tax", "STP / PAYGW"
                'Macro7
            Case Else
                'DoSomething?
            End Select
        End Select



    Next cell

End Sub

如果能用，请告诉我。