Assigning Macro : How to assign macro to Hyperlink ?

.

So far we have always seen running any Macro or Function using a Command Button. Sometimes, instead of putting a Command Button, you want to do the same operations but by using a Hyperlink in Excel. In this article, you are going to learn how to run a macro by using Hyperlink in Excel.

7 Simple Steps to Assign a Macro to a Hyperlink

Step 1. Select the Cell Where you want to make the Hyperlink
Step 2. Right Click –> Hyperlink…
Step 3. Enter the Address of the Same cell where you are making the hyperlink and Give the name to the Link. See the below picture:

Assign Macro to a Hyperlink

Assign Macro to a Hyperlink

Step 4. Click Ok.
Step 5. HyperLink is created.
 
Note: Clicking on this Hyperlink, will do nothing because it is assigned to the same Cell Address.
 
Step 6. Now Press Alt + F11
Step 7. Copy paste the below Code as shown in Picture


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'Check if the Target Address is same as you have given
    'In the above example i have taken A4 Cell, so I am
    'Comparing this with $A$4
    
    If Target.Range.Address = "$A$4" Then
        'Write your all VBA Code, which you want to execute
        'Or Call the function or Macro which you have
        'written or recorded.
        MsgBox "Write your Code here to be executed"
        Exit Sub
    End If
End Sub

In the Above Code we are comparing the Cell Address and then Executing a Set of Code or Function. There is another way of doing this also. We can Compare with the Target Name and execute the Code. In the above example as i have given the Name of the Hyperlink Target as MyMacro.


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'Check if the Target Name is same as you have given
    'In the above example i have given the Name of the HyperLink
    'is MyMacro.
    
    If Target.Name = "mymacro" Then
        'Write your all VBA Code, which you want to execute
        'Or Call the function or Macro which you have
        'written or recorded.
        MsgBox "Write your Code here to be executed"
        Exit Sub
    End If
End Sub

To Check out Excel Macro Tutorials, visit Excel Macro Tutorial

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

12 Comments

  1. Michael

    This doesn’t seem to work for me at all.

    the hyperlink executes no code when i use this method.

    when i execute this code directly in the vba editor, it asks me to tell it which macro to execute. hitting a wall here.

    Reply
    • Rob

      Did you ever get an answer to this? I am having same issue

      Reply
  2. Matthijs

    It is working. I wrote the macro first under “this workbook” in VBA but you need to write the macro in the sheet where the hyperlink is created.

    Reply
  3. Nisha

    I have created Hyperlink through code in VB macro.But in excel file hyper link is creating but only two words only..While saving it as pdf. Hyper link generates for full.

    Plz help why hyper link is not creating full in excel.

    ActiveCell.FormulaR1C1 = “Please Note: This Promotion is subject to the HPE Promotion Terms & Conditions published on”
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=”https://partner.hpe.com/group/upp-apj/article-display/-/l4-display/oVnVhQJ9iOTv/content/id/191324090″, TextToDisplay:=ActiveCell.FormulaR1C1

    Reply
  4. Ajd

    The reference in the code needs to be the reference of the cell which holds the hyperlink… if it is a merged cell, it needs to be the full cell range address e.g. $A$1:$A$4

    include anchors

    Reply
  5. Mai

    This worked 🙂 thanks for the code..

    Reply
  6. Chris

    Can confirm this doesn’t work. Instructions need clarifying. Clicking the cell does nothing, even in it’s raw form as posted here I’d expect a MsgBox but nothing.

    Reply
  7. Chris

    This code worked fine for me. Do you have any suggestions for making it dynamic? I have a seperate report that runs, and the list of results will be hyperlinked based on if there is a corrisponding match in antoher sheet or not. Ideally would love to reference as Worksheets(“User Sheet”).Cells(R,C)

    Thanks!

    Reply
  8. Chris

    Actually, i just figured it out! Worksheets(“User Sheet”).Cells(R,C).Address gets the job done.

    Thanks anyways!

    Reply
  9. Alphonse

    Nice article, well structured and instructive. However, you failed to mention that the code need to be placed in the worksheet’s code in which the hyperlinks are created, and that there can be only one, but within which a lot of things can be done according to the range where the HL resides.
    For those who don’t know, in the VB Editor’s Project Explorer (Alt+F11), right-click on the worksheet with your HL and select View Code. Paste the code provided in the article under Option Explicit
    Cheers.

    Reply

Trackbacks/Pingbacks

  1. VBA - Hyperlink to sort data - […] document.write(''); have a look at this Welcome to LearnExcelMacro.com Assigning Macro : How to assign macro to Hyperlink ?…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest