Hyperlinks

Last Updated on January 14, 2022 – 3 min read

What is a hyperlink? In computing, a hyperlink is a reference to data. You can follow it by clicking. For example, the following hyperlink goes to the best practice article on this blog.

When you click on a hyperlink, a window open. The link can be to a folder, a file, a web page, another sheet, another workbook.

You will see in this article, how to create or delete a hyperlink in your Excel spreadsheet by using a VBA macro.

Hyperlink Sheet in VBA

How do you create a hyperlink in Excel with VBA?

You can use Hyperlinks.Add method attached to the sheet object. Assign the direction of the hyperlink through the SubAddress argument. The cell contains the text to the link.

Below, the following example creates a link on the first sheet to others sheets of your workbook.

Sub Create_Sheets_Hyperlinks()

    'Declarations
    Dim Sh As Worksheet
    Dim i As Integer
    
    i = 1
    
    'Clear the range
    Sheets("Crypto").Range("A:A").Clear
    
    'Loop on all sheets
    For Each Sh In Worksheets
        
        Sheets(1).Cells(i, 1).Select 'Select a value
        'Add the hyperlink in A1
        Sheets(1).Hyperlinks.Add Anchor:=Selection, Address:="", _
            SubAddress:=Sh.Name & "!A1", TextToDisplay:=Sh.Name
            i = i + 1
    
    Next Sh
    
End Sub

Hyperlink Web Page in VBA

To create a hyperlink to a web page, you can select a sheet from the Excel workbook and use the Hyperlinks.Add method by setting the URL of the site in question.

Sub Text_Display_Hyperlink()

    'Add hyperlink and change the name to display
    ActiveSheet.Hyperlinks.Add Range("A1"), Address:="https://vbaskill.com", _
        TextToDisplay:="VBA Skills"
        
End Sub

With VBA, you can open a link leading to a web page.

To launch a link, you can use the FollowHyperlink method by specifying as an argument the site address. You can indicate if you want to open a new window with the boolean type argument NewWindow.

Sub Follow_Hyperlink_Website()

    'Launch the hyperlink to the website
    ActiveWorkbook.FollowHyperlink _
        Address:="https://vbaskill.com", NewWindow:=True
        
End Sub

Hyperlink Folder in VBA

It is also possible to launch a hyperlink to a folder on your computer by using FollowHyperlink.

Sub Follow_Hyperlink_Folder()
    
    'Launch the hyperlink to a folder
    ActiveWorkbook.FollowHyperlink Address:="/Users"
    
End Sub

Delete a hyperlink in a cell

To delete a hyperlink in a cell, use the Hyperlinks.Delete property on the range.

Sub Delete_Hyperlink()

    'Clear hyperlinks
    Range("A1").Hyperlinks.Delete
    Range("A1").Clear
    
End Sub

Delete all hyperlinks in a sheet

You can use Hyperlinks.Delete property directly on your sheet. It deletes all hyperlinks created.

Sub Remove_All_Hyperlinks_Sheet()

    'Clear all hyperlinks in the sheet
    Sheets("Hyper").Hyperlinks.Delete
    
End Sub