CODEWITHSUNDEEP
excelvba
Vaupell
Vaupell

Reputation: 105

Is it possible to use cell hyperlink to run a macro?

I'm generating lines and changes and want to automate the linking with a macro However i wish to add the link to the macro in a clickable cell.

I tried using the same way i would link a URL, to get started then i need to figure out if i can assign the "SubAddress:=" to a macro instead of a link? 

ws.Range("H6").Hyperlinks.Add anchor:=ws.Range("H6"), Address:="", SubAddress:="runMACRO", TextToDisplay:="Show tasks"

Update SOLVED --

the solution i went with was link my macro as "screentip" then following this to run it ;) 

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

Upvotes: 0

Views: 2104

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Yes, you can, but not in that way... A trick must be used. Making a hyperlink for the cell itself and then using the WorksheetFollowHyperlink event for the Target.Parent.Address. Look here, please...

Upvotes: 1

Netloh
Netloh

Reputation: 4378

As stated in the comments, you can make use of the Workbook_SheetFollowHyperlink event handler. Hence, you would have to, in the ThisWorkbook module, to add something like the following code which would call runMACRO whenever a hyperlink in cell H6 is clicked.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    If Target.Range.Address = "$H$6" Then
        Call runMACRO
    End If
End Sub

Upvotes: 1

Related Questions