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!

Submeg

IT

About these ads
Comments
16 Responses to “Excel VBA Tips: Running macros using hyperlinks”
  1. Jeroen says:

    very useful! Thanks

    Jeroen

  2. christian.a says:

    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?

    • submeg says:

      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

  3. Karin says:

    Thank you so much! Works like a charm :)

  4. Jim says:

    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?

    • submeg says:

      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

  5. bert vermeulen says:

    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…

    • submeg says:

      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.

  6. mostafa anssary says:

    what if hyperlink in a shape on the excel

  7. Felipe Mujica says:

    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

  8. JulesBo says:

    That’s a cool solution. But why not add a button to run the macro?

    • submeg says:

      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

  9. João Paulo says:

    Is there a tip to do it in mouse rollover cases? e.g. If the TargetRangeAddress is a Hyperlink formula

Tell me what you think!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Views:

    • 283,013 hits
  • submeg.fm: 30,377 songs 6,160 artists
    AND GROWING...
Follow

Get every new post delivered to your Inbox.

Join 846 other followers

%d bloggers like this: