Returns the formula for a hyperlink [=Hyperlink(...) ] to be inserted in a cell.
This is the safest way I found across my 25 years with Excel to insert a hyperlink function to a cell in a sheet in an outside workbook.
This is combination of functions (aka formula) to generate the proper link to a cell to be used as hyperlink.
If you just need the actual formula not the VBA of it, you can use http://xlfxs.com/hyperlink/ instead.

What is this?



Original Work
Function HyperlinkFunction(ToSheet, ToCell1, ToCell2, LinkCaption)
    ' Will return the full formula to a hyperlink function to be put into a cell
    '    Formula now is expected to create hyperlink to a cell in the same workbook, passing sheetname and cell address (or range)
    '    Formula uses CELL function to extract workbook name from same workbook as formula.
    ' HyperLink2Calc = "=HYPERLINK(""[""&MID(CELL(""filename"",R1C1),SEARCH(""["",CELL(""filename"",R1C1))+1, SEARCH(""]"",CELL(""filename"",R1C1))-SEARCH(""["",CELL(""filename"",R1C1))-1)&""]'Calc'!" & Col1 & ":" & Col2 & """,""Calc"")"
    ' Expected usage ... Range("A1").formular1c1 = HyperlinkFunction("sheet1", "R1C1", "R1C1", "1st Sheet Cell")
    Rett = ""
    Rett = "=HYPERLINK(""[""&MID(CELL(""filename"",R1C1),SEARCH(""["",CELL(""filename"",R1C1))+1, SEARCH(""]"",CELL(""filename"",R1C1))-SEARCH(""["",CELL(""filename"",R1C1))-1)&""]"
    Rett = Rett & "'" & SheetName & "'!" ' Sheet name passed
    Rett = Rett & ToCell1 & ":" & ToCell2 ' Cell range to link to
    Rett = Rett & """,""" & LinkCaption & """)" ' what to show in cell
    Rett = Rett & ""
    HyperlinkFunction = Rett
End Function

ToSheet, ToCell1, ToCell2, LinkCaption

Views 91

Downloads 47