Run macro when clicking on hyperlink that directs

Question

I have two worksheets. Sheet1 has a list of work orders (Column A) and Sheet2 has a list of material numbers that were purchased against the work orders. There will only be one work order entry in Sheet1 but there can be multiple material numbers linked to one work order.

Sheet2 will look something like this;

ColumnA ColumnB

Order1 Material1

Order1 Material2

Order2 Material1

Order3 Material1

I have a hyperlink-vlookup combo that once a work order number is clicked in Sheet1 it will go to the same work order number in Sheet2 where the material is listed.

Now I want to run a macro after the hyperlink is clicked so that Sheet2 is filtered to only show material that were purchased against that work order. I tried the following which did not work. Please help!

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 
     MsgBox "Run Code"   
End Sub 

Answer 1

The issue that you have is that the FollowHyperlink isn't triggered on a calculated hyperlink (i.e. not a static hyperlink but generated via the HYPERLINK function). You could get around this by using the SelectionChange event and testing for a hyperlink. This will need to be inserted into the sheet object

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim linkAddress As String
    On Error Resume Next
    linkAddress = Target.Hyperlinks(1).SubAddress
    On Error GoTo 0
    If linkAddress = vbNullString Then
        MsgBox "Run Code"
    End If
End Sub

or use the solution that I have marked as duplicate in the comments