excel - 模拟指向另一个表中相应单元格的超链接

Question

当我双击一个表格中的单元格时，如何模拟指向另一个表格中相应单元格的超链接？我收到 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

另外，如何为 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

Answer 1

这就是我将如何做到的

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

在将具有该值的 ListColumn 中，执行 Find 以查看它是否在其中。如果是，请选择它。