In Excel, can I use a hyperlink to run vba macro?

Question

I have a spreadsheet that has many rows of data. I would like to be able to click on a cell that will run a macro using the data from that row. Since the number of rows will always be changing, I though a hyperlink for each row might be the best way.

   ROW MeterID   Lat    Long   ReadX  ReadY  ReadZ   CoeffA  CoeffB  CoeffC
   2   10f62gs   34.1   33.3   102.2  231.3  382.2   4.34    22.1    0.002
   3   83gs72g   34.4   31.4   109.2  213.1  372.1   2.23    12.7    0.023
   4   43gS128   33.3   32.2   118.8  138.7  241.8   1.94    5.08    0.107

Is there a way to run a vba macro from clicking on a hyperlink and being able to know the row of the cell that clicked on the hyperlink?

Answer 1

Yes you can, follow the below Simple Steps to do so:

Step 1. Select the Cell Where you want to make the Hyperlink Step 2. Righ Click –> Hyperlink… Step 3. Enter the Address of the Same cell where you are making the hyperlink and Give 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

Run Excel Macro by Clicking on a Hyperlink

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  

Answer 2

I think rather than go through the hassle of creating a hyperlink for each cell, you would be better off creating a macro that references the Activecell property. Next, create a keyboard shortcut for the macro to run. To do so:

1. Press ALT+F8
2. Select Options
3. Choose a key to be the shortcut key

If you already have hyperlinks, triggering a macro using the Hyperlink_Follow event may be best. If not, then consider my recommendation.

Answer 3

This will work for you

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        MsgBox "Row " & ActiveCell.Row & " is clicked"
End Sub