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.
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