excel - 将工作簿子限制为跨工作表的范围数组

Question

如何将工作簿子限制为跨工作表的范围数组？我曾想过使用标签，但我只能Continue:用第一个范围触发。

Private Sub Workbook_SheetBeforeDoubleClick(ByVal sH As Object, ByVal Target As Range, Cancel As Boolean)
    Dim rIncl(1) As Range, i As Integer, r As Range
    Set rIncl(0) = Range("Table1[Column1]"): Set rIncl(1) = Range("Table2")
    For i = 0 To 1
        For Each r In rIncl(i)
            If r.Parent Is sH Then
                If Not Intersect(Target, r) Is Nothing Then GoTo Continue:
                Else: Exit Sub
            End If
        Next
    Next
    
Continue:

标签： excelvba

Answer 1

这应该有效：

Private Sub Workbook_SheetBeforeDoubleClick(ByVal sh As Object, _
                                            ByVal Target As Range, _
                                            Cancel As Boolean)
    Dim rIncl(1) As Range, i As Long, r As Range

    'example ranges to check
    Set rIncl(0) = Range("Table1[Column1]")
    Set rIncl(1) = Range("Table2")

    For i = LBound(rIncl) To UBound(rIncl)
        If rIncl(i).Parent.Name = sh.Name Then    'same sheet?
            Set r = Nothing
            Set r = Application.Intersect(Target, rIncl(i))
            If Not r Is Nothing Then
                'found a match: do something with r
                Debug.Print r.Address
                Exit For 'unless ranges might be overlapping
            End If
        End If
    Next i

End Sub