|
|
|
|
|
|
Excel
|
Dynamic hyperlink formula
|
|
Ease of Use
|
Easy
|
Version tested with
|
97, 2000, 2002, 2003
|
Submitted by:
|
byundt
|
Description:
|
Inserts a HYPERLINK formula pointing to a worksheet cell, possibly from a different worksheet or workbook. The formula readjusts itself automatically if the target cell is moved.
|
Discussion:
|
Ordinary hyperlinks made with HYPERLINK function or Insert...Hyperlink menu item are static?they don't adjust if the target cell is moved such as by inserting or deleting rows. If you always want a hyperlink to point to a specific piece of data (and don't want to create a named range for that purpose), then you can use the CELL function to return a dynamic text reference to the address of the target cell. The formula is tricky to build, especially when referring to cells in other workbooks, so a macro may be helpful. Here's an example of the type of formula required: =HYPERLINK("[C:\VBA\Insert hyperlink.xls]" & CELL("address",Sheet2!$A$1),"Friendly name for my link").
Another use of the sub is to insert hyperlinks in a shared workbook. This is normally not possible because the Insert...Hyperlink menu item is grayed out.
|
Code:
|
instructions for use
|
Sub InsertHyperlink()
Dim cel As Range
Dim flPath As String, frmla As String, friendly As String
On Error Resume Next
Set cel = Application.InputBox("Please select the target cell you want to link to", _
Title:="Hyperlink Function Builder", Type:=8)
If Err <> 0 Then Exit Sub
On Error GoTo 0
friendly = InputBox("Please enter the text you want displayed in the cell containing the link", _
Title:="Hyperlink Function Builder", Default:=cel.Address(external:=True))
If cel.Parent.Parent.Path <> "" Then flPath = cel.Parent.Parent.Path & Application.PathSeparator
frmla = "=HYPERLINK(""[" & flPath & cel.Parent.Parent.Name & "]"" & " & _
"CELL(""address"",'" & cel.Parent.Name & "'!" & cel.Address & "),""" & friendly & """)"
ActiveCell.Formula = frmla
End Sub
|
How to use:
|
- Copy above code
- ALT + F11 to open the VBE
- Use the Insert...Module to create a blank module sheet
- Paste the code there
- Press Alt + Q to close the VBE
- Save workbook before any other changes.
- NOTE: The HYPERLINK formula built by the sub needs to use the CELL function to be dynamic. It is therefore not possible to hyperlink to a range of cells. Instead, the link will take you to the top left cell of the range you specify and activates it.
|
Test the code:
|
- ALT + F8 to open the Macro selector
- Choose the InsertHyperlink macro and click the "Run" button
- In the first input box, navigate to the target cell you want to link to, then click OK
- In the second input box, enter the text you want displayed in your link, then click OK
- Click on the cell containing the HYPERLINK formula to make sure it is working correctly
|
Sample File:
|
Insert hyperlink.zip 9.89KB
|
Approved by mdmackillop
|
This entry has been viewed 312 times.
|
|