Excel 超链接.添加锚点语法/参数

Question

下面是我用来跟踪对 excel 文档的更改的一些代码。我在粗体位上收到运行时错误"5"无效的过程调用或参数"。我认为问题是超链接.Add 锚点的语法或参数，因为当我转到下一行时，"锚点"不会大写。我的参数和语法是否正确？

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
 sSheetName = "1107"
If ActiveSheet.Name <> "LogDetails" Then
 Application.EnableEvents = False
 Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1,0).Value = ActiveSheet.Name & " - " & Target.Address(0, 0)
 Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = OldValue
 Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
 Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
 Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
 **Sheets("LogDetails").Hyperlinks.Add anchor:=Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & sSheetName & "'!" & OldAddress, TextToDisplay:=OldAddress**

 Sheets("LogDetails").Columns("A:D").AutoFit
 Application.EnableEvents = True
 End If
 End Sub

Answer 1

设置SubAddress时考虑删除撇号

用Excel记录的示例：

ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:="", SubAddress:= _
    "Sheet1!A1", TextToDisplay:="Sheet1!A1df"

法典

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Excel.Application
    .EnableEvents = False
End With
Dim sSheetName As String
    sSheetName = "1107"
    If ActiveSheet.Name <> "LogDetails" Then
        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " - " & Target.Address(0, 0)
        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = OldValue
        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
        Dim hlink_cell As Range
        Set hlink_cell = Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5)
        Sheets("LogDetails").Hyperlinks.Add anchor:=hlink_cell, Address:="", SubAddress:=sSheetName & "!" & OldAddress, TextToDisplay:=OldAddress
        Sheets("LogDetails").Columns("A:D").AutoFit
    End If
With Excel.Application
    .EnableEvents = True
End With
End Sub