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.
Download Workbook
You can download the free practice Excel workbook from here.
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.
Read More: Excel Hyperlink to Another Sheet Based on Cell Value
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.
Read More: How to Hyperlink Multiple Cells in Excel (3 Ways)
Similar Readings
- [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe
- How to Link Picture to Cell Value in Excel (4 Quick Methods)
- Why Do My Excel Links Keep Breaking? (3 Reasons with Solutions)
- [Fixed!] ‘This workbook contains links to other data sources’ Error in Excel
- [Fix]: Excel Automatic Update of Links Has Been Disabled
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.
Read More: How to Update Hyperlink in Excel Automatically (2 Ways)
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.
Read More: Excel VBA: Add Hyperlink to Cell in Another Sheet (2 Examples)
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.
Related Articles
- 7 Solutions for Greyed Out Edit Links or Change Source Option in Excel
- [Fix:] Edit Links in Excel Not Working
- Excel Hyperlink to Cell in Another Sheet with VLOOKUP (With Easy Steps)
- How to Find and Replace Hyperlinks in Excel (3 Quick Methods)
- Excel VBA: Open Hyperlink in Chrome (3 Examples)
- [Fix:] Hyperlink to Website Not Working in Excel