Link Hyperlinks to Excel Macro

It is possible to link an Excel hyperlink to a VBA macro with an event.  To start with you need to create a Hyperlink in an Excel Sheet.

On a cell Lets Say L1 Type the word you want to use.

Now right click on L1 and select Hyperlink

A Popup will appear after you click on Hyperlink which looks like the following.

Choose Place in This Document on the Right, Click on the Sheet the Hyperlink is on and Click OK at the bottom.

Your hyperlink should now be created.  It is a Hyperlink at this stage which just selects L1 of the sheet you are on.

Now Press ALT F11 and place the following code in the Sheet Module which the Hyperlink is on.

To do so double click on the Sheet Object in the VBA Project pane and paste the following code in the VBE Code Window.

The following is the code to paste into your sheet object module.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 'Excel VBA

If Target.Range.Address="$L$1" Then
Call LastUsedCol
End If
End Sub

Place the code you want to use when the hyperlink is clicked in a regular Module.  For demonstration purposes this is the code I have used.

Sub LastUsedCol() 'VBA to trap the last used column starting in Row 1.
Dim lc As Integer
lc=Range("IV10").End(xlToLeft).Column
Cells(10, lc).Interior.Color=vbBlue
End Sub

Whenever the hyperlink is clicked a macro of your choosing should run.

For a file with more of these sort of Hyperlinks see this article. Trapping Dynamic Ranges