CODEWITHSUNDEEP
excelvba
Jordan_b
Jordan_b

Reputation: 315

Get explicit sheet hyperlink from cell VBA excel

I am looking for a way to extract the hyperlink of an excel cell with hyperlink to another sheet in the same workbook. Is there a way to do it? In example:

Cell A1: "HPI" (with hyperlink to Sheet HPI)
result -> Cell B1: HPI

I found this formula but it's not working with reference to another sheet. 

Function GetURL(cell As range, Optional default_value As Variant)
'Lists the Hyperlink Address for a Given Cell
'If cell does not contain a hyperlink, return default_value
 If (cell.range("A1").Hyperlinks.Count <> 1) Then
      GetURL = default_value
Else
      GetURL = cell.range("A1").Hyperlinks(1).Address
End If
End Function

Upvotes: 3

Views: 289

Answers (1)

Chronocidal
Chronocidal

Reputation: 8081

A hyperlink within a document uses the .Subaddress property, not the .Address property:

Function GetDestination(Target As Range, Optional default_value As Variant) AS Variant
'Lists the Hyperlink Address or Subaddress for a Given Cell
'If cell does not contain a hyperlink, return default_value
    If (Target.Cells(1, 1).Hyperlinks.Count <> 1) Then
        GetDestination = default_value
    Else
        With Target.Cells(1, 1).Hyperlinks(1)
            If Len(.Address)>0 Then
                GetDestination = .Address
            Else
                GetDestination = .SubAddress
            End If
        End With
    End If
End Function

Upvotes: 5

Related Questions