CODEWITHSUNDEEP
excelvba
user2319146
user2319146

Reputation: 381

Simulate a hyperlink to a corresponding cell in another table

When I double-click on a cell in one table, how can I simulate a hyperlink to a corresponding cell in another table? I am getting a type mismatch error with sCellAddress.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Set tb = ActiveSheet.ListObjects("Table1")
    If Not Intersect(Target, tb.ListColumns("Site").DataBodyRange) Is Nothing Then
        Cancel = True
        Dim sCellAddress As String
        sCellAddress = [CELL("address", INDEX(Sites[Base], MATCH([@Site], Sites[Site], 0)))]
        Application.Goto ActiveSheet.Range(sCellAddress)
    End If
End Sub

Separately, how can I specify multiple ranges for RangeExclude?

Private Sub Workbook_SheetBeforeDoubleClick(ByVal sH As Object, ByVal Target As Range, Cancel As Boolean)
    Dim ShExclude As Worksheet, RangeExclude As Range
    Set ShExclude = ThisWorkbook.Worksheets("Sheet1")
    Set RangeExclude = ShExclude.ListObjects("Table1").ListColumns("Sites").DataBodyRange
End Sub

Upvotes: 1

Views: 41

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33175

Here's how I would do it

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim loFrom As ListObject
    Dim loTo As ListObject
    Dim rFound As Range
    
    Set loFrom = Me.ListObjects("tblOne")
    Set loTo = Me.ListObjects("tblTwo")
    
    If Not Intersect(loFrom.ListColumns("Site").DataBodyRange, Target) Is Nothing Then
        Set rFound = loTo.ListColumns("Site").DataBodyRange.Find(Target.Value, , xlValues, xlWhole)
        If Not rFound Is Nothing Then
            rFound.Select
        End If
    End If
    
End Sub

In the ListColumn that will have the value, do a Find to see if it's in there. If it is, select it.

Upvotes: 2

Related Questions