Hyperlinks

A hyperlink is a link from a hypertext document to another location which can be activated by clicking on a highlighted word or image, for example the hyperlink follow go to the best practice article in the blog to use in VBA.

Hyperlinks correspond to an URL link attached to a value. When we click on, a windows is openned. It can be a folder, a file, a web page, another sheet , another workbook

You can insert a hyperlink in your Excel spreadsheet by using a VBA macro.

Hyperlink for a Sheet in VBA

How to create a hyperlink to an Excel sheet ? To do it, we use the property Hyperlinks.Add attached to the sheet object. Then we can assign the direction of the hyperlink through the SubAddress argument as well as the word to write in the cell containing the text to the link.

The following example creates a link on the first sheet to each sheet of the Excel workbook.

Sub CreateSheetsHyperlinks()

    'Declarations
    Dim Sh As Worksheet
    Dim i As Integer
    
    i = 1
    
    'Clear the range
    Sheets("BITCOIN").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 for a Web Page in VBA

As above, to create a hyperlink to a web page, simply select a sheet from the Excel workbook and use the Hyperlinks.Add method by setting the link of the site in question as the address.

Sub TextDisplayHyperlink()

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

You can also with a VBA code directly open a link leading to a web page. To directly launch a link you can use the FollowHyperlink method by specifying the address of the site as an argument and also by indicating that you want to open a new window with the boolean type argument NewWindow.

Sub FollowHyperlinkWebsite()

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

Hyperlink for a Folder in VBA

It also possible to directly launch a hyperlink to a folder on your system using FollowHyperlink.

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

Delete a hyperlink

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

Sub DeleteHyperlink()

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

Delete all hyperlinks in a Sheet

You can also use the Hyperlinks.Delete property directly on the sheet, this will delete all the hyperlinks created.

Sub RemoveAllHyperlinksInSheet()

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

Leave a Reply

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s