I'm guessing you're in 2007. I not aware of HL_SheetName functionality in
2003. Or is this a UDF?

Barb Reinhardt

"spwmarluk" wrote:

Hi,

I have following function:

Function HL_SheetName(komorka As Range) As String
If komorka.Hyperlinks.Count = 0 Then
Exit Function
End If
Dim poz As Integer
HL_SheetName = komorka.Hyperlinks(1).SubAddress
poz = InStr(1, HL_SheetName, "!")
poz = poz - 1
HL_SheetName = Mid(HL_SheetName, 1, poz)
poz = InStr(1, HL_SheetName, "'")
If poz = 1 Then
poz = Len(HL_SheetName)
poz = poz - 2
HL_SheetName = Mid(HL_SheetName, 2, poz)
End If
End Function

This function returns SubAddress from hyperlink ( actually name of the
Sheet ).
It works, but sometimes I get wrong sheet name, and hyperlink works
well.
For example:

Cell A1 have hyperlink to Sheet2!A1.
I enter the formula in B1: HL_SheetName(A1) , and I get in B1: Sheet2.

Cell A2 have hyperlink to Sheet3!A1.
I enter the formula in B2: HL_SheetName(A2) , and I get in B2: Sheet2.

WTF??
Both hyperlinks works fine.
I don't understand.

With the hyperlinks(1).TextToDisplay is the same situation...

Where exactly is the destination address in the structure of
hyperlink? ( SubAddress, Address , ..... ?? )