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.

Table of Contents hide

Download Workbook

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

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

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

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

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

Conclusion

Download Workbook

You can download the free practice Excel workbook from here.

Add Hyperlink to Cell Value.xlsm

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

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.

VBA Code Explanation

Sub HyperlinkAnotherSheet()

To name the sub-procedure of the macro.

Worksheets("Sheet1").Select

Selects the worksheet containing the cell whose value will be linked.

Range("B5").Select

Declares the cell whose value will be linked.

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

Defines the destination. Cell A1 of Sheet2 will be opened after clicking the cell value (B5).

End Sub

To leave the sub-procedure of the macro.

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.

VBA Code Explanation

Sub HyperlinkMultipleSheets()

To name the sub-procedure of the macro.

Dim iSheet As Worksheet

Declares the variable to store the worksheet.

Worksheets("Sheet3").Select

Selects the worksheet containing the cell whose value will be linked.

Range("B5").Select

Declares the cell whose value will be linked.

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

Starts iterating through every sheet in the active workbook. It will go through each row and link them with the worksheets, starting from the very first sheet to the last one by setting the destination to Cell A1 of every worksheet. It continues to do this till it finishes iterating through every sheet in the active workbook.

End Sub

To leave the sub-procedure of the macro.

Similar Readings

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.

VBA Code Explanation

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

This piece of code declares the range B1:B10 for inserting the word “exceldemy“.

Application.EnableEvents = False

To turn off any kind of events to take place while the code is running.

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

Every time you enter the specified word (“exceldemy“) in no other range than B1:B10, then it will be linked with the address of “https://www.exceldemy.com/“.

Application.EnableEvents = True

Turning back on the EnableEvents property of the application.

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.

VBA Code Explanation

Option Explicit

In the beginning, the code force declares all the variables explicitly of the file.

Sub HyperlinkWebsite()

Then, names the sub-procedure of the macro.

Dim iRange As Range

After that, declares the variable to hold the selected range.

On Error Resume Next

If any error occurs, then go to the next statement.

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

Later, defines the selection operation from the user input.

Dim i As Long
Dim iLink As String

Declaring the variables to perform loop operation.

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

Starts looping from the last row count to the first row.

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)

To link the selected cell with the link address of “https://www.exceldemy.com/“.

End If

End of IF statement.

Next

End of FOR loop.

End Sub

In the end, leaves the sub-procedure of the macro.

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.