|
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.
 
|
