2

How do I assign a macro to a hyperlink?

ThinkingStiff
  • 64,767
  • 30
  • 146
  • 239
EKet
  • 7,272
  • 15
  • 52
  • 72

1 Answers1

3

You can do it using the Worksheet_FollowHyperlink event.

For example I recorded a macro named Macro1 and the following code will run the macro whenever the hyperlink is clicked

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        Run ("Macro1")
End Sub

But this is not a very effective solution. My hyperlinks points to the same sheet (an by default to the 1st cell) so when ever the hyperlink is clicked the first cell in this sheet will be selected automatically.

I didn't investigate more on this. you can simply cancel the navigation (don't know if possible) or set the hyperlink property to the current cell so that the selection stays in the same cell.

Shoban
  • 22,920
  • 8
  • 63
  • 107
  • Set the hyperlink to the cell it's in - that's what I do. It goes nowhere, but the code runs. – Dick Kusleika Dec 16 '10 at 20:59
  • yeah I tried this but the problem is if you have a set cells you want to add this macro then you cannot simply click and drag to apply the link. The link for all the cells will point to the first cell (hope I am clear :-) ) so in short if you want to have this functionality for many cells then you will have to add the hyperlinks manually – Shoban Dec 16 '10 at 21:05
  • 1
    Hi @Shoban by the help of your answer, I could solve a big problem for me. You can also see another example of this use in below link: [http://stackoverflow.com/questions/43360114/how-to-expand-a-group-in-excel-by-using-hyperlinkor-by-maybe-assigning-macro-to] – Mertinc May 11 '17 at 03:39