7 Simple Steps to Assign a Macro to a Hyperlink
Step 1. Select the Cell Where you want to make the Hyperlink
Step 2. Right Click –> Hyperlink…
Step 3. Enter the Address of the Same cell where you are making the hyperlink and Give the name to the Link. See the below picture:
Assign Macro to a Hyperlink
Step 4. Click Ok.
Step 5. HyperLink is created.
Note: Clicking on this Hyperlink, will do nothing because it is assigned to the same Cell Address.
Step 6. Now Press Alt + F11
Step 7. Copy paste the below Code as shown in Picture
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'Check if the Target Address is same as you have given
'In the above example i have taken A4 Cell, so I am
'Comparing this with $A$4
If Target.Range.Address = "$A$4" Then
'Write your all VBA Code, which you want to execute
'Or Call the function or Macro which you have
'written or recorded.
MsgBox "Write your Code here to be executed"
Exit Sub
End If
End Sub
In the Above Code we are comparing the Cell Address and then Executing a Set of Code or Function. There is another way of doing this also. We can Compare with the Target Name and execute the Code. In the above example as i have given the Name of the Hyperlink Target as MyMacro.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'Check if the Target Name is same as you have given
'In the above example i have given the Name of the HyperLink
'is MyMacro.
If Target.Name = "mymacro" Then
'Write your all VBA Code, which you want to execute
'Or Call the function or Macro which you have
'written or recorded.
MsgBox "Write your Code here to be executed"
Exit Sub
End If
End Sub
This doesn’t seem to work for me at all.
the hyperlink executes no code when i use this method.
when i execute this code directly in the vba editor, it asks me to tell it which macro to execute. hitting a wall here.
Did you ever get an answer to this? I am having same issue
It is working. I wrote the macro first under “this workbook” in VBA but you need to write the macro in the sheet where the hyperlink is created.
I have created Hyperlink through code in VB macro.But in excel file hyper link is creating but only two words only..While saving it as pdf. Hyper link generates for full.
Plz help why hyper link is not creating full in excel.
ActiveCell.FormulaR1C1 = “Please Note: This Promotion is subject to the HPE Promotion Terms & Conditions published on”
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=”https://partner.hpe.com/group/upp-apj/article-display/-/l4-display/oVnVhQJ9iOTv/content/id/191324090″, TextToDisplay:=ActiveCell.FormulaR1C1
The reference in the code needs to be the reference of the cell which holds the hyperlink… if it is a merged cell, it needs to be the full cell range address e.g. $A$1:$A$4
include anchors
This worked 🙂 thanks for the code..
You are welcome!! thanks for stopping by.
Can confirm this doesn’t work. Instructions need clarifying. Clicking the cell does nothing, even in it’s raw form as posted here I’d expect a MsgBox but nothing.
This code worked fine for me. Do you have any suggestions for making it dynamic? I have a seperate report that runs, and the list of results will be hyperlinked based on if there is a corrisponding match in antoher sheet or not. Ideally would love to reference as Worksheets(“User Sheet”).Cells(R,C)
Thanks!
Actually, i just figured it out! Worksheets(“User Sheet”).Cells(R,C).Address gets the job done.
Thanks anyways!
Thank you
Code works very well
Nice article, well structured and instructive. However, you failed to mention that the code need to be placed in the worksheet’s code in which the hyperlinks are created, and that there can be only one, but within which a lot of things can be done according to the range where the HL resides.
For those who don’t know, in the VB Editor’s Project Explorer (Alt+F11), right-click on the worksheet with your HL and select View Code. Paste the code provided in the article under Option Explicit
Cheers.