[Excel VBA] How to Insert Hyperlinks in Cells | External and Internal Link Settings

As part of improving business efficiency with Excel, many users wish to automate the process of setting hyperlinks in cells.

In this article, I will carefully explain how to use VBA to insert links to external websites and links to other cells within the sheet, accompanied by concrete code.

Intended Use and Purpose

• Insert a specific Web page URL into a cell.
• Automatically create a link that jumps to another sheet when clicked.
• Configure settings in bulk using VBA instead of manual operation.

VBA Code Example

The following sample code inserts an external link (Web site) into cell B3 and a link to a cell in another sheet into cell B4.

Sub InsertHyperlinks()

    With ActiveSheet.Hyperlinks
        ' Insert external link (Web page) into cell B3
        .Add _
            Anchor:=Range("B3"), _
            Address:="https://morinokabu.com", _
            TextToDisplay:="Morinokabu Site (External Link)"

        ' Insert internal sheet link into cell B4 (Jump to Sheet2, C5)
        .Add _
            Anchor:=Range("B4"), _
            Address:="", _
            SubAddress:="Sheet2!C5", _
            TextToDisplay:="Go to Sheet2 Cell C5"
    End With

End Sub

Explanation of the Code

About the .Add Method

The Hyperlinks.Add method specifies the following three main arguments:

Distinguishing Between External and Internal Links

• External Links (Web Sites): Set the URL in Address and omit SubAddress.
• Internal Sheet Links: Leave Address empty and specify the jump destination cell reference in SubAddress.

Common Use Cases

• Creating a table of contents for the sheet and jumping to the relevant page from each item.
• Inserting links from each product data entry to a detailed page (Web site).
• Batch setting links from a summary sheet to individual input sheets within the file.

Summary

By utilizing VBA, you can easily auto-insert hyperlinks into multiple cells within Excel.

Since it supports both external Web pages and internal movements within the workbook, it is effective in various situations such as creating reports, manuals, and linking with business systems.

By applying the code introduced in this article, you can reduce the effort of manually creating links and achieve accurate link settings without errors. Please customize it according to your business needs.