How to add Hyperlinks in Excel using VBA Macro

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
        addHyperLink
    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").Columns(1).ClearContents
    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
ErrHandler:
    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)

Also Read: Copy or Move Files from One Folder to Another using VBA (Popular Post)

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.

Syntax

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

Also Read: Split Cell Values with Carriage Returns to Multiple Columns using VBA Macro

Conclusion

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 →

How to add Hyperlinks in Excel using VBA Macro

Tools you can use

Syntax