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