Hey there, let's break down that runtime error '5' you're hitting with your Excel change tracking code. First off, let's clear up one thing right away: VBA is case-insensitive, so the lowercase anchor instead of Anchor isn't causing your problem. That's just VBA being relaxed about syntax capitalization—no need to stress over that part.

Runtime error '5' means you're making an invalid procedure call or passing a bad parameter to Hyperlinks.Add. Since you suspect the Anchor parameter, let's focus on what that parameter requires and where things might be going wrong:

Key Rules for the Anchor Parameter #

The Anchor argument in Hyperlinks.Add must be a valid Range or Shape object that exists in your workbook. If you pass a Range that doesn't exist (or reference the wrong worksheet), you'll get this error every time.

Common Mistakes That Trigger This Error #

Here are the most likely issues with your code:

1. Missing Worksheet Qualification
   If you're writing something like Anchor:=Range("C" & lastRow) without specifying the worksheet (e.g., LogDetails.Range("C" & lastRow)), VBA will default to the active sheet. If the active sheet isn't LogDetails, you're trying to add a hyperlink to a cell in the wrong sheet—one that might not even exist in the context of your code.

2. Non-Existent LogDetails Worksheet
   If your LogDetails sheet hasn't been created yet, any attempt to reference its cells will result in an invalid Range object. You need to check if the sheet exists first before using it.

3. Invalid Row/Column Reference
   If your calculation for the last row (or target cell) is off (e.g., returning 0 or a row number beyond the worksheet's limit), the Range you pass to Anchor will be invalid.

Fixed Example Code #

Here's a revised version of your Workbook_SheetChange procedure that addresses these issues:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim sSheetName As String
    Dim logSheet As Worksheet
    Dim lastRow As Long
    
    sSheetName = "1107"
    
    ' First, check if LogDetails sheet exists—create it if not
    On Error Resume Next
    Set logSheet = ThisWorkbook.Worksheets("LogDetails")
    On Error GoTo 0
    
    If logSheet Is Nothing Then
        Set logSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        logSheet.Name = "LogDetails"
        ' Add header for clarity
        logSheet.Range("A1:E1").Value = Array("Timestamp", "Sheet", "Cell", "Old Value", "New Value")
    End If
    
    ' Skip changes to the log sheet itself to avoid infinite loops
    If Sh.Name = "LogDetails" Then Exit Sub
    
    ' Only track changes on the specified sheet
    If Sh.Name = sSheetName Then
        lastRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1
        
        ' Log basic change info
        logSheet.Cells(lastRow, "A").Value = Now()
        logSheet.Cells(lastRow, "B").Value = Sh.Name
        
        ' Add hyperlink with properly qualified Anchor
        logSheet.Hyperlinks.Add _
            Anchor:=logSheet.Cells(lastRow, "C"), _
            Address:="", ' Leave blank for internal workbook links
            SubAddress:=Sh.Name & "!" & Target.Address, _
            TextToDisplay:=Target.Address
        
        ' Note: To capture old values, you'll need to store them in SheetSelectionChange event
        ' since SheetChange fires after the value is already updated
        logSheet.Cells(lastRow, "E").Value = Target.Value
    End If
End Sub

What This Fixes #

• Explicit Worksheet References: Every Range (including the Anchor) is tied directly to logSheet, so there's no ambiguity about which sheet we're targeting.
• Sheet Existence Check: We create LogDetails if it doesn't exist, so we never try to reference a non-existent sheet.
• Valid Range Calculation: lastRow is calculated using logSheet.Rows.Count to ensure we're always targeting a valid row in the log sheet.

Quick Troubleshooting Steps #

If you still get the error after this:

• Double-check that lastRow returns a valid number (use Debug.Print lastRow to verify).
• Confirm that Target.Address is a valid cell reference (no invalid characters or out-of-bounds ranges).
• Make sure you're not trying to add a hyperlink to a merged cell (merged ranges can cause issues with Anchor parameters).

内容的提问来源于stack exchange，提问作者MsAgentM