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() 'Puts a dynamic hyperlink formula in the active cell. Formula automatically adjusts if its target is moved. Dim cel As Range Dim flPath As String, frmla As String, friendly As String 'Select a target cell in this workbook, or any other open workbook 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 'If user hits 'Cancel', then an error occurs--and sub is exited On Error GoTo 0 'Build strings for the "friendly name" displayed in the link, and for the dynamic HYPERLINK formula friendly = InputBox("Please enter the text you want displayed in the cell containing the link", _ Title:="Hyperlink Function Builder", Default:=cel.Address(external:=True)) 'This next statement avoids an error if the target workbook hasn't been saved 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 'Put the HYPERLINK formula in the active cell End Sub

How to use:

  1. Copy above code
  2. ALT + F11 to open the VBE
  3. Use the Insert...Module to create a blank module sheet
  4. Paste the code there
  5. Press Alt + Q to close the VBE
  6. Save workbook before any other changes.
  7. 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:

  1. ALT + F8 to open the Macro selector
  2. Choose the InsertHyperlink macro and click the "Run" button
  3. In the first input box, navigate to the target cell you want to link to, then click OK
  4. In the second input box, enter the text you want displayed in your link, then click OK
  5. 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.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express