Solved: Call Macro From Hyperlink

[tonyrosen]

How would I go about calling a macro (MyMacro) via a hyperlink in my workbook (on the same sheet)?

[Ken Puls]

Can I ask why you'd want to run a macro from a hyperlink? The standard methods would be from a forms button, activex button, change event, menu item or toolbar button...

Anything special about hyperlink that you'd prefer it that way?

[Shazam]

Here is a sample:



[VBA]
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Select Case Target.Parent

Case "Sheet3!A1"

Call MyMacro1

Case "Sheet3!A2"

Call MyMacro2

End Select
End Sub
[/VBA]

[tonyrosen]

kpuls,

the "standard" around this place is to use Hyperlinks, not buttons. From a button is the only way I've done it. Hence, I have no clue how to from a Hyperlink.

Shazam,

SHAZAAM!

[tonyrosen]

I Ended up using this:


[VBA]
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Not Intersect(Target.Parent, Range("C9")) Is Nothing Then
MySub
End If
End Sub
[/VBA]

[brunces]

tonyrosen,

You could also try this:

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C9")) Is Nothing Then
MyMacro
End If
End Sub
[/vba]

Worked fine for me.

-----

Ken Puls,

I once needed something like this as well. I had a file full of sheets and macros. The first sheet had a "menu" with plenty of buttons. Some of these buttons simply called sheets and cells, but others called macros. Then I decided to get rid of those buttons and use hyperlinks only, so that my menu would have a cleaner and smoother layout (buttons took much space). That's when I realized I couldn't call macros from hyperlinks. So I had to use something like the above code to get my hyperlinks working with macros.

-----

Hugs, fellas!

Bruno