How to add Hyperlinks in Excel using VBA Macro

← PrevNext →

A Hyperlink is a reference to add a link between two data in two different locations. We have seen and used links on websites. Similarly, we can add links to data in Excel sheets too. Here, in this article we will see how we can add Hyperlinks in Excel using VBA. All we need is a small macro or program, that will a hyperlink based on certain conditions.

Here is a scenario. I have added a small amount of data on Sheet1 showing annual sales for different regions for the First year of business. The figures are not real. There is another column with header Second Year. I will add figures for every region.

The program (macro) checks if the figure in the Second year is less than the First year. If the amount is less, I want the macro to add a link next to the figure and add another link in Sheet2 linking to the figure in Sheet1 (with less amount).

Add Hyperlinks in Excel using VBA Macro

The VBA Code
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 1 Then Exit Sub             ' IF ITS A HEADER, DO NOTHING.

    If Trim(Target.Text) <> "" Then
    End If

End Sub

Sub addHyperLink()

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Dim myDataRng As Range
    Dim cell As Range
    Set myDataRng = Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    Application.Worksheets("Sheet2").Cells(1, 1) = "Critical Log"
    For Each cell In myDataRng
        If cell(myDataRng.Row, 1).Text < Val(cell(myDataRng.Row, 0).Text) Then
            ' ADD ADDRESS.
            Application.ActiveSheet.Hyperlinks.Add _
                Anchor:=Application.ActiveSheet.Cells(cell.Row, cell.Column + 1), _
                    Address:="mailto:arunbanik21@rediffmail.com?subject=Sales Report", _
                        SubAddress:="", _
                            ScreenTip:="Critical", _
                                TextToDisplay:="Mail this Figure"
            ' ADD SUB-ADDRESS
            Application.Worksheets("Sheet2").Hyperlinks.Add _
                Anchor:=Application.Worksheets("Sheet2").Cells(cell.Row, 1), _
                    Address:="", _
                        SubAddress:=Application.ActiveSheet.Name & "!" & cell.Address, _
                            ScreenTip:="Critical", _
                                TextToDisplay:="Check Figure"
        End If
    Next cell

    Set myDataRng = Nothing
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The first method will add a Hyperlink (in the same sheet) if the entered amount is less than the previous year's amount.

The second method adds a Hyperlink in Sheet2 (first column). It does have an Address, but a SubAddress linking back to the cell with less amount in Sheet1. (See picture above)

Hyperlinks.Add() Method

I have used this method twice in my example. Therefore, it is important that I explain the method and its properties.

The Hyperlinks.Add() method will add a hyperlink to a specified range and it takes five parameters.


Add(Anchor As Object, Address As String, [SubAddress], [ScreenTip], [TextToDisplay]) as Object

The first two parameters are required and the rest are optional.

Anchor (required) – An excel Range to add the anchor for the Hyperlink
Address (required) – The address value of the Hyperlink
SubAddress (optional)– SubAddress property of the Hyperlink
ScreenTip (optional) – Looks similar to a Tooltip. Shows a little message when we hover the mouse over the Hyperlinks.
TextToDisplay (optional) – The Hyperlink label showing an underlined text for users to click the link


Well, now you know how to add Hyperlinks to your Excel sheet, dynamically. This little piece of code will spare you from adding hyperlinks manually to each cell and in the process save precious time. You do not have to worry about missing links anymore.

Thanks for reading. 🙂

← PreviousNext →