Introduction
Link to a
chart
Link to a subroutine in the
Visual Basic Editor
Execute
a subroutine through a hyperlink
Leverage the FollowHyperlink event procedure
In Excel, one creates a hyperlink with Insert | Hyperlink... A hyperlink can be to a web page or an existing file or to a new Excel workbook. See the options on the left side of the dialog box below.
In addition to linking to a file or a web page, one can link to a more specific location within the file or page. This is called a bookmark. In a web page a bookmark is identified with an anchor tag that has a name. An example of one is below.
<a name="_A_function_that_returns a range of "></a>
By contrast, in Excel one doesn't have to explicitly identify a bookmark since it is any cell in the workbook. To specify a bookmark as the target of the hyperlink, click the Bookmark... button in the Hyperlinks dialog box. In the resulting dialog box (see below), select the sheet of interest. The default cell will be A1 and one can change it as desired. In the example below, the hyperlink bookmark will be to Sheet3 cell A13. One can also link to any named range.
Since a bookmark has to be to a cell, it is not possible to create a hyperlink to a chart. However, for a chart embedded in a worksheet, one can always hyperlink to the cell beneath the chart! In the example below, the top-left cell beneath the chart is cell C8. One can now create a hyperlink to that cell and it will appear that the link is to the chart.
Of course, this solution fails when applied to a chart in its own sheet. For that one must use a VBA approach. This is discussed in the Leverage the FollowHyperlink event procedure section.
One of the capabilities that Excel supports with a hyperlink is the ability to locate particular subroutine in the Visual Basic Editor. While the value of such a capability is not immediately obvious, it can be done.
In XL97 one can enter the name of a subroutine through the UI in the "sub address" or bookmark field. In XL2000+ it has to be done programmatically with code. For a shape, the code would be something like
ActiveSheet.Hyperlinks.Add ActiveSheet.Shapes(1), _
ThisWorkbook.Name, "ABC"
For a cell, it would be
ActiveSheet.Hyperlinks.Add Range("c6"), ThisWorkbook.Name, "ABC"
Clicking the link will take one to the subroutine in the VBE.
Adding parenthesis after the subroutine name will cause the subroutine to be executed.
However, it will be called *twice* followed by a "Reference is not valid" error.
However, all is not lost. One can still use a hyperlink through the FollowHyperlink event procedure. With very little code - and code that needs *no* active maintenance! - this can be very effective. Not only can one execute a subroutine of interest but the same technique allows one to link to a chart, embedded in a worksheet or in its own sheet.
This event procedure is available only in XL2000 or later.
The first step is to establish a convention that the displayed text of the hyperlink is the name of the procedure to call. For visual (non)effect, the hyperlink address should be the workbook itself and the subaddress (i.e., the bookmark) should be the address of the cell containing the hyperlink.
Now, in the FollowHyperlink procedure all one needs is
Application.Run Target.TextToDisplay
One can take this a step or two further and have the FollowHyperlink code try and figure out what object to activate! For example, the following code first tries to runs a subroutine. If that fails, it tries to locate an embedded chart in the same sheet as the hyperlink. If that fails, it tries to activate a chartsheet in the workbook containing the hyperlink. In each case, the code looks for a 'target' with the same name as the TextToDisplay property of the hyperlink. The code goes into the ThisWorkbook code module.
Option Explicit
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
ByVal Target As Hyperlink)
On Error Resume Next
Application.Run Target.TextToDisplay
If Err.Number = 0 Then Exit Sub
Sh.ChartObjects(Target.TextToDisplay).Activate
If Err.Number = 0 Then Exit Sub
Sh.Parent.Charts(Target.TextToDisplay).Activate
End Sub
Consider an example. The image below is of a worksheet. Clicking each of the three links will result in a different action. Clicking the ABC link will run the subroutine named abc. Clicking the Embedded Chart link will cause the code to select the embedded chart by that name. Clicking the final hyperlink will cause the code to activate the chart sheet Chart by itself. Of course, in each case one must have correctly named targets, i.e., there must be a subroutine named abc, an embedded chart named Embedded Chart, and a chart sheet named Chart by itself.
