Geek Answers Handbook

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

I have a spreadsheet with many rows of data. I would like to be able to click on the cell that will run the macro using the data from this row. Since the number of rows will always change, I believe that a hyperlink for each row may 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 the vba macro by clicking on the hyperlink and recognizing the row of the cell that clicked on the hyperlink?

+8
vba excel-vba excel
source share
3 answers

It will work for you

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

Yes, you can follow these simple steps:

Step 1. Select the cell in which you want to make a hyperlink Step 2. Righ Click → Hyperlink ... Step 3. Enter the address of the same cell where you make the hyperlink and specify the name of the link. See image below:

Assign macro to hyperlink

Step 4. Click OK. Step 5. HyperLink is created.

Note. Clicking on this hyperlink will do nothing, because it has the same cell address.

Step 6. Now press Alt + F11. Step 7. Copy the paste below the code.

Run the Excel macro by clicking on the 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 compare the address of the cell, and then perform a set of code or function. There is another way to do this. We can compare with the target name and execute the code. In the above example, since I gave the name of the hyperlink object 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
+7
source share

I think, instead of overcoming the difficulties of creating a hyperlink for each cell, you better create a macro that refers to the Activecell property. Then create a key combination to run the macro. For this:

  • Press ALT + F8
  • Choose Options
  • Select a key for a keyboard shortcut

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

0
source share

More articles:


All Articles