XL2000: How to Return the Source of a Hyperlink in a Cell (274159)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. For more information about how to use the sample code in this article, click the article number below to view the article in the Microsoft Knowledge Base:

How to Create a Hyperlink

To create a hyperlink to an Excel worksheet or to a cell in the same workbook, follow these steps:

1. Select a cell in an Excel workbook. Then, click Hyperlink on the Insert menu.
2. In the Text to display box, type a name for the hyperlink.
3. If you want the link to open another workbook, click File under Browse for. Select your workbook, and then click OK.
   
   -or-
   
   If you want the link to refer to a sheet in the current workbook, follow these steps:
   1. Click Place in This Document under Link to.
   2. Type the cell that you want to link to in the Type the cell reference box, or type a defined name. Include the sheet name as part of the cell reference, or Excel uses the first sheet by default.
      
      NOTE: If you click Cell Reference under Or select a place in this document, the Type the cell reference box is unavailable. Anything that you previously typed is lost, and you must select a sheet name to make the box available.
4. Click OK.

How to Obtain the Source of the Hyperlink

The following macros replace the contents of the hyperlink's target with the source address of the hyperlink, when you click the hyperlink.

1. Press ALT+F11 to switch to the VBA Editor.
2. In the Project Explorer window, double-click This Workbook.
3. In the Code window for the workbook, type one of the following sample macros, based on the target of the hyperlink.

   Method 1: Return Source of a Hyperlink to the Same Sheet

   Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
       ByVal Target As Hyperlink)
       x = Target.Range.Address
       ActiveCell.Value = x
   End Sub
   					

   Method 2: Return Source of a Hyperlink to the Same Workbook

   Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
       ByVal Target As Hyperlink)
       x = Sh.Name & ":" & Target.Range.Address
       ActiveCell.Value = x
   End Sub
   					

   Method 3: Return Source of a Hyperlink to a Different Workbook

   Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
       ByVal Target As Hyperlink)
       y = Target.Application.ThisWorkbook.Name
       x = Sh.Name & ":" & Target.Range.Address & " " & y
       ActiveCell.Value = x
   End Sub
   					

How to Display the Address

NOTE: The macros as written replace the contents of the hyperlink's target with the source address of the hyperlink. If you would rather have a message box display the source address of the hyperlink, replace

ActiveCell.Value = x
				

with

MsgBox x
				

NOTE: The code in this article does not work in Microsoft Excel 97 because the SheetFollowHyperlink event does not exist in Excel 97.