Hyperlink in Excel Add-in with code example

Hyperlink in Excel Add-in with code example

Hyperlink

Excel offers great features and hyperlink is one of them. Where you can put external links to refer specific cell value which can be used to follow. In Excel a hyperlink can be placed within workbook or external sources. There are many methods and properties supported by hyperlinks object we will see below:

Syntax

Range.Hyperlinks.Add(Anchor, Address, ScreenTip)

Parameters

  1. Anchor: refer to the range where a hyperlink needs to be attached.
  2. Address: location to be followed upon click.
  3. ScreenTip: a tool tip text shown upon mouse hover

Add

Public Sub InsertHyperLink()
    'object to get current selection
    Dim oRange As Range
    Set oRange = Selection
    
    oRange.Hyperlinks.Add Anchor:=Range("E4"), Address:="https://www.vbaoverall.com", ScreenTip:="Click to navigate VBAOVERALL portal"
        
    'Cleanup
    If Not oRange Is Nothing Then
        Set oRange = Nothing
    End If
End Sub

Output

C# code example

private void btnAddLink_Click(object sender, RibbonControlEventArgs e)
{
	//retain selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	oRange.Hyperlinks.Add(Anchor: oRange, Address: "https://www.vbaoverall.com",
		ScreenTip: "This is a hyperlink");
}

Output

In this example I put “click here to open www.vbaoverall.com” text in a cell and make selection now click Link button from the ribbon it will add hyperlink to selected cell as shown in the screenshot.

Follow

If you wish to programmatically click on the hyperlink the follow method gives you that power. Let’s put some code which will determine if given cell value is > 5000 then it will automatic trigger hyperlink attached to next cell which navigate to respective product sheet as shown below:

VBA code example

Put following code on Sheet_Change event and try changing value to any Column “B”, Row 2 or 3 to more than 5000 and you will see the magic.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Replace(Target.Address, "$", "") = "B2" Then
        If Target.Value > 5000 Then
            Range("c" & Target.Row).Hyperlinks(1).Follow
        End If
    End If
    If Replace(Target.Address, "$", "") = "B3" Then
        If Target.Value > 5000 Then
            Range("c" & Target.Row).Hyperlinks(1).Follow
        End If
    End If
End Sub

Output

Next : Add shapes in Excel sheet using C#

Leave a Reply

Your email address will not be published.