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!

Submeg

IT

Posted by:submeg

Musician, Writer and Inspirer. I discuss the projects I am working on, the services I provide and my general thoughts.

16 replies on “Excel VBA Tips: Running macros using hyperlinks”

  2. Need advice…i have a range with a hyperlink (delete macro) on row 1, table/form on rows 2 thru 9, then another hyperlink (add macro) on row 10. When you click hyperlink on row 10, macro will copy rows 1 thru 9 and insert rows after row 9 therefore pushing add macro hyperlink down to row 19. How can i change the example code above so that i don’t need to specify the address of hyperlink?

    Like

    Reply

    1. Hi Christian,

      From your description, I have some questions. When you copy this data, will the hyperlink move down 10 rows each time? (I think the answer is yes to this). Are all the cells above the hyperlink containing data? Will the link always remain in the same column? If the answer is yes to all these questions – Then you can make the following modification:

      Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

      n = Worksheets(“Sheet1”).Range(“A:A”).Cells.SpecialCells(xlCellTypeConstants).Count

      Location = “$A” & “” & “$” & n & “”

      If Target.Range.Address = Location Then ‘will vary depending on where how many rows there are
      ‘Place the macro name here
      Exit Sub
      End If

      End Sub

      Like

      Reply

  4. I like. The one problem with this approach, for me, is that if cells are later added above or to the left of that cell that contains a link to itself, the link no longer points to the original cell (assuming cell reference used in link). I could get around that by using named ranges in the link instead, but that makes creating these more cumbersome, and I could end up with many, many of them. This would clutter up the name list, and keeping these (meaningless) names unique could become an issue… Any other way of avoiding links ending up no longer pointing to themselves?

    Like

    Reply

    1. Hi Jim,

      Yes I have run into that problem myself. I am trying to look at a way to do this dynamically, but haven’t been able to wrap my head around it just yet.

      I hope there is another piece of code that could be used that would allow for this to happen; the current way is quite restrictive. When I have time (whenever that is!) I will want to look into switching to a better/faster alternative.

      To answer your question, that particular way of linking does not avoid this. I will have a look at what I have been trying and perhaps you might be able to figure out what I am missing?

      Leigh

      Like

      Reply

  5. I have used this in a lot of sheets, and i was wondering if it’s possible to place this in a module so i the code is a bit cleaner…

    Like

    Reply

    1. I have never tried to put it into a module, but I’m not too sure. It may work, but it would need to be global, otherwise it most likely couldn’t be read depending on where you were in the spreadsheet at the time.

      Like

      Reply

  7. Hi Submeg,

    I’ve been trying, without success until now, to create a macro that deletes the hyperlink’s row, when clicked.

    Your Sub adapted by me:

    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = ActiveSheet.Name & “!” & ActiveCell.Address Then del
    Exit Sub
    End If
    End Sub

    My del function:

    Sub del()
    ActiveCell.Rows(“1:1”).EntireRow.Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Select
    End Sub

    I’m obviously not getting it.
    F

    Like

    Reply

    1. Hi Jules,

      Yes you could do this, however there are situations when using a hyperlink is more beneficial. Just providing another way to achieve the same result. Are there any other ways you could think of that would be useful? Always looking for different excel solutions.

      Submeg

      Like

      Reply

Tell me what you think!