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?

Burkhart

Answer 1

This will work for you

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

Jelena

Answer 2

The more interesting way of hyperlink to run a macro, looks to be the next approach (Hyperlink formula). It does not need any event:

1. Write a hyperlink formula in a cell. This can be done in VBA, too:

    Sub testCreateHyperlinkFunction()
       'Very important to have # in front of the function name!
       Range("A1:A5").Formula = "=HYPERLINK(""#MyFunctionkClick()"", ""Run a function..."")"
    End Sub

A more spectacular use will be the next approach, able to keep the initial cells value of the processed range ("A1:A5" in example):

Sub testCreateHyperlinkFunctionBis()
  Dim rng As Range, arr As Variant, c As Range, i As Long
   Set rng = Range("A1:A5")
   arr = rng.Value
   For i = 1 To UBound(arr, 1)
     Range("A" & i).Formula = "=HYPERLINK(""#MyFunctionkClick()"", " & _
            IIf(IsNumeric(arr(i, 1)), arr(i, 1), """" & arr(i, 1) & """") & ")"
   Next i
End Sub

2. Create the function to be called (in a module):

    Function MyFunctionkClick()
      Set MyFunctionkClick = Selection 'This is required for the link to work properly
      MsgBox "The clicked cell addres is " & Selection.row
    End Function

Do note the Set MyFunctionkClick = Selection line really is needed. The function needs to know, somehow, to what cell the code is referring. If this is missing, the function is called twice and you get a "Reference is invalid" error.

3. Clicking the cell, the function MyFunctionkClick() runs...

Lebanon

Interesting stuff, I didn't know functions could be called using this #Function() in a hyperlink syntax! Although I just tried a very simple application of it and the function is being called twice, and then I get a message saying "reference isn't valid" - weird, I will investigate – Jannet

@ jamheadart: Neither me, until I discovered it (I do not remember where and when...). I have some such pieces of code in my collection or interesting stuf. Nobody knows everything... In my opinion, it is important to learn and be able to appreciate what it is really interesting and may be useful :) About your problem, it is an error in your function. Its weakness is that it does not allow step by step debugging. When an error exists, it does not raise an error. It behaves in this way Working twice and issuing a semi elocvent message. The code does not stop on error! Very careful on coding – Lebanon

Definitely no way to know everything, but that's what keeps it interesting, if we knew it all we would be bored!! For my issue, I tried literally just =HYPERLINK("#MyUdf()","Click me") in a cell, and then in Module1 had a function Public Function MyUDF(): MsgBox "wow": End Function – Jannet

@ jamheadart: You missed the core of the function... :) Set MyUDF = Selection – Lebanon

Lol yup, I didn't even realise that line was there or what it was doing. Works fine now, that's awesome. Thanks! – Jannet

@ jamheadart: Maybe it will be good to edit my answer and emphasize that also... I thought it will be self understood. Did it... – Lebanon

Yeah I didn't realise that bit was totally necessary, I thought you were just using that as an example of how to use the Selection properties, I thought it would just call the function and a simple "MsgBox" would be called fine. – Jannet

@ jamheadart: The function needs to know, somehow, to what cell is the code referring. Based on that, the function can be made to differently return... It also returns, but in that strange way, if the function does not know its root. – Lebanon

@Lebanon this is an excellent implementation for my distributed software (I want to avoid using sheet specific code). I wrote a little wrapper function that can be used to run any sub from: Function runFromHyperlink(procName): Set runFromHyperlink = selection: application.Run procName: End Function. The hyperlink formula would then be: =HYPERLINK("#runFromHyperlink(""myUdf"")","Click me") using @Jannet 's example. This is useful as you don't need a wrapper function for each sub with the Set MyUDF = Selection. – Nonpartisan

And MyUDF can then be any Sub or Function too without the Set MyUDF = Selection syntax. I could even pass some arguments to the procedure like: =HYPERLINK("#runFromHyperlink(""foo(5)"")","Run procedure 'foo' with argument of 5") or =HYPERLINK("#runFromHyperlink(""foo(True)"")","Run procedure 'foo' with argument True"). I couldn't get it to work for string arguments though. – Nonpartisan

This is really useful, but from what I've tried, it doesn't seem to work when used within a dynamic array formula. – Edmondson

@Lebanon I opened a Q on how to call a module in a remote WB. All my modules are stored separatley from the ActiveWorkbook which the # is shorthand for, if you have a moment! #69590967 – Loathly

It should be possible, but not directly... I will look to the question and place an answer if I will be able to understand what you need and I will have a solution. – Lebanon

This method is nice, but unfortunatelly it's dangerous, because any EnableEvents = off, or Calculation = xlManual no longer works.. :( – Pickard

@Dumitru Daniel Why "dangerous"? Can it harm anything and I missed it? The joking part... :) The above suggested solution does not need events enabled and does not care about Calculation... The function is called directly when the cell is clicked. It does not use the FollowHyperlink sheet event. It has other weaknesses in terms of difficult error handling, but I only wanted showing a way of calling a function using Hyperlink formula, not the event... – Lebanon

I have a little issue with this. I try to use this to filter an autofilter on a different sheet. i.sstatic.net/m9jqV.png If I enable the msgbox then that works, and it also debug prints correctly but it does not filter. If I step through the code then it filters correctly but it seems it just skips the changing of sheets and filtering – Discontinuation

Answer 3

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

1. Select the Cell Where you want to make the Hyperlink
2. Righ Click –> Hyperlink…
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

4. Click Ok.
5. HyperLink is created.

Note: Clicking on this Hyperlink, will do nothing because it is assigned to the same Cell Address.

6. Now Press Alt + F11
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  

Madoc

Answer 4

I'd just like to add another approach inspired by one of the answers here, that I've used in the past. It means you don't have to create hyperlinks or a helper column to initiate processing the row, you can just double click on any cell in the row you want to process:

On the sheet you want to have the double-clicking work use this:

Private Sub Worksheet_Activate()
Application.OnDoubleClick = "Module1.ProcessRow"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnDoubleClick = ""
End Sub

And then in Module1 have a routine that will process the active cell:

Sub processRow()
MsgBox "Row " & ActiveCell.Row & " on " & ActiveSheet.Name & " was clicked"
End Sub

You should also disable the Excel double-click method in the workbook events:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnDoubleClick = ""
End Sub

I find the double-click method is really, really handy and intuitive for a lot of processes e.g. bringing up a UserForm with data populated from the row or transferring that data to another sheet maybe with calcs or formatting etc.

Jannet

Answer 5

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.

Griffon

Note: Hyperlink_Follow fires AFTER Hyperlink is opened – Loathly

Answer 6

Just played with =HYPERLINK() approach and found, that this code will work to create hyperlinks in Excel cells:

Sub testCreateHyperlinkFunction()
    Dim r As Range
    Set r = Range("A1")
    ' use SubAddress arg of Hyperlinks.Add method to link our function
    r.Hyperlinks.Add _
        Anchor:=r, _
        Address:=vbNullString, _
        SubAddress:="MyFunctionkClick()", _
        TextToDisplay:="Hyperlink text" 

    ' note: TextToDisplay arg is desirable here, 
    ' otherwise Excel will insert MyFunctionkClick() here

End Sub

Function MyFunctionkClick()
  Set MyFunctionkClick = Selection ' required!
  MsgBox "The clicked cell addres is " & Selection.row
End Function

Advantages:

• Range.Hyperlinks(1).Follow works;
• protection from unwanted link change from user;
• uniform "look & fill" with other links in document;

Disadvantages:

• debugging of MyFunctionkClick() still unavailable;
• no way to change hyperlink from Excel GUI - only from code;

Deannadeanne

Answer 7

Tried to use a VBA macro which is called by a hyperlink.
It's task is to insert one row below the row containing the =HYPERLINK.
VBA seems to ignore .Insert

Function HyperlinkFnc_AddRow()
  Set HyperlinkFnc_AddRow = Selection
  MsgBox "Next line of code: Adding a row below selected row " & Selection.Row
  Rows(Selection.Row + 1).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Function

Sub Button_AddRow()
  ActiveSheet.Shapes(Application.Caller).TopLeftCell.Select
  HyperlinkFnc_AddRow
End Sub

Observation
When HyperlinkFnc_AddRow() is invoked by a hyperlink function
(e.g. =HYPERLINK("#HyperlinkFnc_AddRow()";"Invoking Function ""HyperlinkFnc_AddRow()"" by this hyperlink should add row below"):

1. "MsgBox" gets control.
2. The following line with ".Insert" IS IGNORED (a row is NOT inserted).

Invoking Sub Button_AddRow() by a button shows, that the code in function "HyperlinkFnc_AddRow" works as intended (a row gets inserted).
Getting this solved would be helpful.

Acknowledgment

ActiveSheet.Shapes(Application.Caller).TopLeftCell.Select Determines the cell in which the button is placed and selects it. – Acknowledgment