Microsoft Office Tutorials and References
In Depth Information
RUN A MACRO FROM A BUTTON
There are two dropdowns above the code pane. From the left dropdown,
choose Worksheet. From the right dropdown, choose FollowHyperlink.
You now have the makings of a Worksheet_FollowHyperlink macro.
Every time someone clicks a hyperlink on this worksheet, this bit of code will
run. The Target variable tells you about the hyperlink that was clicked.
If there is only one hyperlink on the worksheet, then you can simply run the
macro:
Private Sub Worksheet_FollowHyperlink(ByVal Target As
Hyperlink)
TestMacro
End Sub
However, if you have multiple hyperlinks on the worksheet, you can use the
Target.TextToDisplay property to distinguish between hyperlinks:
Private Sub Worksheet_FollowHyperlink(ByVal Target As
Hyperlink)
Select Case Target.TextToDisplay
Case "Run Report 2"
TestMacro
Case "Run Report 2"
TestMacro2
Case "Run Report 3"
TestMacro3
End Select
End Sub
Using a hyperlink to run a macro is a favorite trick when you want to toggle
between different states. Perhaps you want to toggle between sorting ascending
and sorting descending in a column. This macro will perform an action and
change the text in the hyperlink so that a different action can be performed the
next time:
Private Sub Worksheet_FollowHyperlink(ByVal Target As
Hyperlink)
Select Case Target.TextToDisplay
Case "A-Z"
SortMacroAscending
Target.TextToDisplay = "Z-A"
Case "Z-A"
SortMacroDescending
Part
3
 
Search JabSto ::




Custom Search