Excel VBA Tips: Running macros using hyperlinks


Excel VBA Tips: Running macros using hyperlinks

At work today I had to create a workbook that provided links between different sheets within a workbook and turned on a filter. However, I didn’t want to use buttons, as it would be become extremely cluttered.

I discovered that you can run macros using hyperlinks, so commandbuttons are no longer required!

1. Right click on the cell you wish to hyperlink. Select the location as anywhere within the current workbook.
2. Select the developer tab and open up visual basic.
3. In the folder “Microsoft Excel Objects” there is a list of the sheets contained within the workbook. Select the sheet that contains the hyperlink.
4. Paste the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$A$4" Then '$A$4 will vary depending on where the hyperlink is
        'Place the macro name here
        Exit Sub
    End If 

End Sub

The macro that you wish to run is placed where I have mentioned with the comment. The location of the hyperlink is specified where $A$4 is located in the above code.

This is a very useful code, hopefully it helps to make your code and worksheets neater.

Come check out the directory for the rest of my excel tips!