Microsoft KB Archive/274159

= XL2000: How to Return the Source of a Hyperlink in a Cell =

Article ID: 274159

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q274159



SUMMARY
This article demonstrates how to use Microsoft Visual Basic for Applications (VBA) to programmatically obtain the source address (cell reference) of a hyperlink.



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:

212536 OFF2000: How to Run Sample Code from Knowledge Base Articles

How to Create a Hyperlink
To create a hyperlink to an Excel worksheet or to a cell in the same workbook, follow these steps:  Select a cell in an Excel workbook. Then, click Hyperlink on the Insert menu. In the Text to display box, type a name for the hyperlink. 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:  Click Place in This Document under Link to. 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.  Click OK.</li></ol>

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.  Press ALT+F11 to switch to the VBA Editor.</li> In the Project Explorer window, double-click This Workbook.</li>  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 & &quot;:&quot; & 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 & &quot;:&quot; & Target.Range.Address & &quot; &quot; & y   ActiveCell.Value = x End Sub </li></ol>

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.

Additional query words: XL2000 Hyperlink

Keywords: kbhowto KB274159

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.