Case Study

Case Study: Running a Macro from a Hyperlink

Using a trick, it is possible to run a macro from a hyperlink. Because many clients are used to touching a hyperlink to perform an action,this method may be more intuitive for your clients.

The trick is to set up placeholder hyperlinks that simply link back to themselves. Select a cell and select Insert, Hyperlink from the menu or press Ctrl+K. In the Insert Hyperlink dialog, click on Place In This Document. Figure 24.12 shows a worksheet with four hyperlinks. Each hyperlink points back to its own cell.

When a client clicks on a hyperlink,you can intercept this action and run any macro by using the FoiiowHyperiink event. Enter the following code on the code module for the worksheet:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Select Case Target.TextToDisplay Case "Widgets"

RunWidgetReport Case "Gadgets"

RunGadgetReport Case "Gizmos"

RunGizmoReport Case "Doodads"

RunDooDadReport End Select End Sub

Figure 24.12

To run a macro from a hyperlink,you have to create placeholder hyperlinks that link back to their cells.Then, using an event handler macro on the worksheet's code pane,you can intercept the hyperlink and run any macro.

0 0

Post a comment