Dynamic link to last selected cell in other sheet

[jpetoday]

Hi all,
I'm having a hard time getting this code by John, posted below, to work.
At first, it seems to work fine: when I hover over the hyperlink in cell A1, it displays: 'sheet10!B12', which is where I previously clicked, so that's correct. However, when I click on it, I get 'reference is invalid' (not exactly sure what this error is called in English, this is my translation). The strange thing is, the hyperlink works only when I come from 1 specific sheet. For all others, I get the error I mentioned.

Any chance someone can help me out with this?

I can't think of a formula solution, so try this code which goes in the ThisWorkbook module. The code assumes the sheet 11 is named "Sheet11". It creates a hyperlink to the previously selected sheet and cell in cell A1 of "Sheet11".

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name <> "Sheet11" Then
        Worksheets("Sheet11").Hyperlinks.Add Anchor:=Worksheets("Sheet11").Range("A1"), Address:="", _
            SubAddress:=ActiveCell.Parent.Name & "!" & ActiveCell.Address(False, False), _
            TextToDisplay:=ActiveCell.Parent.Name & "!" & ActiveCell.Address(False, False)
    End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name <> "Sheet11" Then
        Worksheets("Sheet11").Hyperlinks.Add Anchor:=Worksheets("Sheet11").Range("A1"), Address:="", _
            SubAddress:=ActiveCell.Parent.Name & "!" & ActiveCell.Address(False, False), _
            TextToDisplay:=ActiveCell.Parent.Name & "!" & ActiveCell.Address(False, False)
    End If
End Sub

 

[GWteB]

Hi @jpetoday, Welcome to the MrExcel message board!

Your code has a minor omission. The fact that worksheet names may contain spaces is not taken into account. The code below is likely to work as expected.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name <> "Sheet11" Then
        Worksheets("Sheet11").Hyperlinks.Add Anchor:=Worksheets("Sheet11").Range("A1"), Address:="", _
            SubAddress:="'" & ActiveCell.Parent.Name & "'" & "!" & ActiveCell.Address(False, False), _
            TextToDisplay:=ActiveCell.Parent.Name & "!" & ActiveCell.Address(False, False)
    End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name <> "Sheet11" Then
        Worksheets("Sheet11").Hyperlinks.Add Anchor:=Worksheets("Sheet11").Range("A1"), Address:="", _
            SubAddress:="'" & ActiveCell.Parent.Name & "'" & "!" & ActiveCell.Address(False, False), _
            TextToDisplay:=ActiveCell.Parent.Name & "!" & ActiveCell.Address(False, False)
    End If
End Sub