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