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

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
  1. 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.

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

This will work for you

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

Tags:

Excel

Vba