Restrict workbook sub to array of ranges across worksheets

Question

How can I restrict a workbook sub to an array of ranges across worksheets? I had thought to use a label, but I can only trigger Continue: with the first range.

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:

None of your range statements are "fully qualified" , i.e. explicitly refer to worksheet and so when you set `rIncl(0) = Range(...` and `rIncl(1) = Range(...` both tables have to be on the same sheet to get what you expect. That seems to be inconsistent wth your statement `...ranges across worksheets` so do you need to fully qualify your range names with the sheet name? https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 — JohnnieL, Feb 15 '21 at 06:12
You may also want to think about defining your included ranges as named ranges in the work book — JohnnieL, Feb 15 '21 at 06:19
I tried with `Worksheets("Sheet#").` but the result is the same. — user2319146, Feb 15 '21 at 06:51

score 1 · Accepted Answer · answered Feb 15 '21 at 07:29

This should work:

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

Thank you. Is it better to declare i as long rather than integer? — user2319146, Feb 15 '21 at 08:20
There’s no advantage using Integer, and Long is less likely to overflow — Tim Williams, Feb 15 '21 at 16:53

Restrict workbook sub to array of ranges across worksheets

1 Answers1