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.
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
' 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 63
Downloads 26
CodeID
DB ID