VBA retrieve hyperlink target sheet?

Question

I'm trying to retrieve the sheet reference location from a hyperlink that's in a cell

The following doesn't seem to work as test doesn't return anything, even though G8 points to Sheet: KO in Cell A19

test = Range("G8").Hyperlinks(3).Address

Now if I run the following:

For Each hl In Sheets("LIST").Hyperlinks
    MsgBox "Range " & hl.Range.Address & " addr " & _
            hl.Address & " text " & hl.TextToDisplay
Next hl

It cycles through and finds the correct address but I can't seem to work out how to detect the sheet it's pointing. Also the loop is a bit of a mess because it errors out once it has found the first and only hyperlink in this situation. And it's not always specific for G8. I guess I could just throw an if statement in and exit the for loop early.

Regardless of that, I can't find anywhere in stackoverflow, google, microsofts "hyperlinks" docs a way to get the sheet name.

See below sample illustration:

Answer 1

SubAddress is what you want:

Sub Test()

    Dim hl As Hyperlink, r As Range

    Set hl = ActiveSheet.Hyperlinks(1)

    Set r = Application.Evaluate(hl.SubAddress)

    Debug.Print "Sheet: '" & r.Parent.Name & "'", "Range:" & r.Address()

End Sub