VBA to Add Hyperlink to Cell Value in Excel (4 Criteria)

Hyperlinks are a very efficient way to go from one location to another with just a single click. Implementing VBA is the most effective, quickest and safest method to run any operation in Excel. This article will show you how you can add the hyperlink to the cell value in the Excel worksheet with VBA macro.

VBA to Add Hyperlink to Cell Value in Excel: 4 Criteria

Following this section, you will learn how to add a hyperlink of a different sheet to a cell value in the current worksheet, how to add hyperlinks of multiple worksheets to multiple cell values in the active spreadsheet, how to insert values in cells that add hyperlinks automatically and how to add a hyperlink to user-selected cell in Excel with VBA.

1. Embed VBA to Add Hyperlink of Different Worksheet to a Cell Value in Active Sheet

Consider the following dataset. In our workbook, we have a value “Click here to go to Sheet2” in Cell B5 of Sheet1.

We will learn the VBA code that will add the link of Sheet2 in the cell value of B5 in Sheet1.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Next, in the pop-up code window, from the menu bar, click Insert -> Module.

• Then, copy the following code and paste it into the code window.

Sub HyperlinkAnotherSheet()
Worksheets("Sheet1").Select
Range("B5").Select
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Sheet2'!A1"
End Sub

Your code is now ready to run.

• Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the successful code execution, look at the following image to see the result.

As you can see from the image above, the cell value of B5 in Sheet1 is now linked with worksheet Sheet2.

To check whether the link really works or not, let’s click on the link.

As you can see in the gif above, if you click on the link created in Cell B5 of worksheet Sheet1, it takes you to the worksheet Sheet2.

Sub HyperlinkAnotherSheet()

Worksheets("Sheet1").Select

Range("B5").Select

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Sheet2'!A1"

End Sub

Read More: How to Get Hyperlink from an Excel Cell with VBA

2. Apply Macro to Add Hyperlink of Multiple Worksheets to Multiple Cell Values in Active Sheet

From the previous section, you have learned how to hyperlink a cell value with a single worksheet in Excel. In this section, you will learn how to hyperlink multiple worksheets to multiple cell values of the active worksheet in Excel with VBA.

Look at the above dataset. We will link values in Cells B5, B6 and B7 of worksheet Sheet3 with the worksheet Sheet1, Sheet2 and Sheet3 with our VBA code.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub HyperlinkMultipleSheets()
Dim iSheet As Worksheet
Worksheets("Sheet3").Select
Range("B5").Select
For Each iSheet In ActiveWorkbook.Worksheets
    ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & iSheet.Name & "!A1" & "", ScreenTip:=""
    ActiveCell.Offset(1, 0).Select
Next iSheet
End Sub

Your code is now ready to run.

• After that, Run the macro as we showed you in the above section. The result is shown in the image below.

As you can see from the image above, the cell values of B5, B6 and B7 from Sheet3 are now linked with worksheets Sheet1, Sheet2 and Sheet3.

To check whether the links really work or not, let’s click on the link.

As you can see in the gif above, if you click on the links created in Cell B5, B6 and B7 of worksheet Sheet3, it takes you to the worksheets of Sheet1, Sheet2 and Sheet3 respectively.

Sub HyperlinkMultipleSheets()

Dim iSheet As Worksheet

Worksheets("Sheet3").Select

Range("B5").Select

For Each iSheet In ActiveWorkbook.Worksheets
    ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & iSheet.Name & "!A1" & "", ScreenTip:=""
    ActiveCell.Offset(1, 0).Select
Next iSheet

End Sub

Read More: Excel VBA: Open Hyperlink in Chrome

3. Implement VBA to Insert Value in Cell and Add Hyperlink Automatically in Excel

Do you want something interesting like when you add any specified value in the cells, the links will be automatically inserted into that cell value?

Let’s see how to do that with VBA macro in Excel.

Steps:

• We will add values in Sheet7 of our Excel worksheet. So, we right-click on the sheet and select View Code from the list of options.

You must insert the code of this section in the code window under the specified sheet, not in any module.

• After that, copy the following code and paste it into the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("B1:B10")) Is Nothing Then Exit Sub
    If LCase(Left(Target.Value, 20)) <> "exceldemy" Then Exit Sub
    Application.EnableEvents = False
    Target.Formula = "=HYPERLINK(""https://www.exceldemy.com/" & Target.Value & """,""" & Target.Value & """)"
    Application.EnableEvents = True
End Sub

• Now, don’t run this code, save it.

If you closely look at the code then you will see, in the 3rd line of the macro, we declare range B1:B10. And in the 4th line of the code, we wrote the word “exceldemy”. We also provided the link to the ExcelDemy website after one line from that. This whole process means that every time you write the word “exceldemy” in the range B1:B10 of your dataset, the word “exceldemy” will be automatically linked to the ExcelDemy website.

Let’s find out whether it really does that or not.

As you can see from the gif above, every time we write the word “exceldemy” in the range B1:B10, it is automatically linked to the website.

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("B1:B10")) Is Nothing Then Exit Sub
If LCase(Left(Target.Value, 20)) <> "exceldemy" Then Exit Sub

Application.EnableEvents = False

Target.Formula = "=HYPERLINK(""https://www.exceldemy.com/" & Target.Value & """,""" & Target.Value & """)"

Application.EnableEvents = True

4. Embed VBA Macro to Add Hyperlink to Cell Value by Selection in Excel

Consider the following dataset. In our workbook, we have a value “Click here to go to ExcelDemy Website” in Cell B5 in Sheet8.

We will see how we can link this value to the ExcelDemy website by selecting Cell B5 manually after the VBA code execution.

Steps:

• At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Option Explicit
Sub HyperlinkWebsite()
Dim iRange As Range
On Error Resume Next
Set iRange = Application.Selection
Set iRange = Application.InputBox("Select Range to Add Link", "Range", iRange.Address, Type:=8)
Dim i As Long
Dim iLink As String
For i = iRange.Rows.Count To 1 Step -1
  If iRange.Cells(i, 1).Value <> "" Then
    iLink = "https://www.exceldemy.com/" & CStr(iRange.Cells(i, 1).Value)
      iRange.Cells(i, 1).Hyperlinks.Add Anchor:=iRange.Cells(i, 1), Address:=iLink, TextToDisplay:=CStr(iRange.Cells(i, 1).Value)
   End If
  Next
End Sub

Your code is now ready to run.

• After you Run the code, there will be a pop-up input box asking you to select the cell that you want to link with.
• Select the cell. In our case, we selected Cell B5.
• Lastly, press OK.

Once you have done all of those successfully, the cell value will be linked with the link that you provided in the code.

As we have provided the link address of the ExcelDemy website in our code, the cell value of B5 is now linked to the website.

Option Explicit

Sub HyperlinkWebsite()

Dim iRange As Range

On Error Resume Next

Set iRange = Application.Selection
Set iRange = Application.InputBox("Select Range to Add Link", "Range", iRange.Address, Type:=8)

Dim i As Long
Dim iLink As String

For i = iRange.Rows.Count To 1 Step -1

If iRange.Cells(i, 1).Value <> "" Then
  iLink = "https://www.exceldemy.com/" & CStr(iRange.Cells(i, 1).Value)
  iRange.Cells(i, 1).Hyperlinks.Add Anchor:=iRange.Cells(i, 1), Address:=iLink, TextToDisplay:=CStr(iRange.Cells(i, 1).Value)

End If

Next

End Sub

Read More: Excel VBA: Add Hyperlink to Cell in Another Sheet

Download Workbook

Conclusion

To conclude, this article showed you 4 effective criteria on how you can add the hyperlink to the cell value in the Excel worksheet with VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.