CODEWITHSUNDEEP
vbaexcelhyperlink
Dullspark
Dullspark

Reputation: 215

Is it possible to create a macro, that will create a hyperlink, that will run a macro

I'm just wondering if you can create a Macro, that will hyperlink a cell and assign a separate macro to it?

I know you can use a hyperlink to run a macro using the following

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
       Call Macro Name
End Sub

I also know you can use a macro to create a Hyperlink using the following

With Worksheets(1) 
 .Hyperlinks.Add Anchor:=.Range("a5"), _ 
 Address:="", _ 
 ScreenTip:="", _ 
 TextToDisplay:= ""
End With

Is there anyway to put these together? Or is there a way to use the second code to assign a macro? instead of an address?

So if I made a macro that takes data from a raw data tab, and puts it into a table like this

Table with Hyperlinks

The macro then needs to assign a hyperlink to the values (4,2,5,2,5) that will run a separate macro when clicked. I'm hoping it would look something like this

With Worksheets(1) 
 .Hyperlinks.Add Anchor:=.Range("B2"), _ 
 Address:=Macro Name, _ 
 ScreenTip:="", _ 
 TextToDisplay:= ""
End With

There is much more to it as the table may have more rows so the hyperlinks will have to have a variable range etc, but i'd just to know if it's possible because I can't get it to work as above

Upvotes: 1

Views: 1218

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

It is possible, but not by default. You will to perform some kind of workaround:

Create a hyperlink on eg cell B2 linking to B2 itself.

.Hyperlinks.Add Anchor:=Range("B2"), Address:="", SubAddress:=Range("B2").Address

And then use the Target to decide which macro should be called, so you can use the address Target.Parent.Address or the cell value Target.Parent.Value to distinguish between the macros.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'decide by address
    Select Case Target.Parent.Address
        Case "$B$2"
            CallMacro1
        Case "$B$3"
            CallMacro1
    End Select

    'or by intersection a range
    Select Case True
        Case Not Intersect(Target.Parent, Range("B4:B8")) Is Nothing
            CallMacro1
    End Select


    'or by value
    Select Case Target.Parent.Value
        Case 2
            CallMacro1
        Case 4
            CallMacro1
    End Select
End Sub

Note that the Call statement is deprecated and should not be used anymore. Just use the macro name itself to call a macro: Instead of Call MacroName just use MacroName.

Alternatively use the ScreenTip as workaround to set the macro name

.Hyperlinks.Add Anchor:=Range("B4"), Address:="", SubAddress:= _
    Range("B4").Address, ScreenTip:="MyMacro"

and then use the ScreenTip to run that macro

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Application.Run Target.ScreenTip
End Sub

Note that the MyMacro has to be public in a Module otherwise you need to use eg. Sheet1.MyMacro to tell in which scope the macro actually is. Otherwise it will throw an error that the macro name does not exist.

Note also that this is a kind of workaround, that miss-uses the ScreenTip.

Upvotes: 2

Related Questions