Excel VBA Hyperlink adress + Subadress

Hello,

 

I stumbled across a Problem regarding adding a Hyperlink to my Excel sheet via vba.

 

ws1.Hyperlinks.Add Anchor:=ws1.Cells(Z, 50), _
                Address:=strHypAdd
                SubAddress:="#" & "'" & tabname & "'" & "!A2"
                TextToDisplay:="'" & strHypAdd & "#" & "'" & tabname & "'" & "!A2"

 

The Problem I have is, that Excel uses the TextToDisplay string as the adress for the Hyperlink. So if I would put the string "Test" after TextToDisplay:= and check the properties of the Hyperlink after I ran the code, the adress would be Test, same as the Alternative Text.

 

If I comment out TextToDisplay, build my address + subaddress beforehand and use it as the Hyperlink address, Excel always converts the "#" to " - " and therefor the link will not work anymore.

 

What I would like to achieve is to have the Hyperlink to my other Excel worksheet and one of the tables within it. But the Name of the Hyperlink should be something else than the link.

 

best regards

 

Patrick