Run Macro When Hyperlink Clicked Issue

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
118
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Hi,

I am hoping someone could point me in the right direction, I am trying to get a macro to run when a hyperlink is clicked.
The below VBA code works when I use Insert Hyperlink in Excel but not when I use the =HYPERLINK function in a formula.

Code To Call Macro When Link Clicked - I have tried both Workbook_Sheet and Worksheet_ but neither work with =HYPERLINK
VBA Code:
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
If Target.Parent.Address = "$A$1" Then
    Call Hello   'test macro
End If
End Sub

Below is an example function, ideally if A1 = 5 then - else show a hyperlink to another sheet within the document else show -.
VBA Code:
=IF(A1="5","-", HYPERLINK("#"&"'"&MONTH($E$3)&"'!E19","…more tasks found."))

When I don't have the macro in the editor the link works and takes me to the relevant sheet, when I have the code in the editor it doesn't do anything.

Hopefully this makes sense and thanks in advance!
t0ny84
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
118
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
If example workbook would help let me know and I can upload one when I am next at work.
 

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
118
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
In case anyone else has this issue:

=HYPERLINK function doesn't work with SheetFollowHyperlink

I figured out the following work around of adding a blank hyperlink to cell referencing itself then adding the formula into the cell.

VBA Code:
Sub createhyperlinks()
'This script simplifies adding they hyperlink, to use select cells then click run.
Dim c As Range
Set c = Selection
         
For Each cell In c
    ActiveSheet.Hyperlinks.Add cell, Address:="#"
        Next
End Sub

Once the blank hyperlink has been added enter your formula into the cell(s) then paste the below code onto ThisWorkbook (or edit for the worksheet option).

e,g, =if(a1=a2,"False","Positive")

VBA Code:
Private Sub Workbook_SheetFollowHyperlink(ByVal sh As Object, ByVal Target As Hyperlink)
  Application.ScreenUpdating = False
  If ActiveCell.Value = "Positive"
     Call hypelink   'This is the name of my macro I wanted called.
Else: Exit Sub

End If
Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,764
Messages
5,574,104
Members
412,569
Latest member
kiteifitswindy
Top