Here is a scenario. I have added a small amount of data on Sheet1 showing annual sales for different regions for the First year of business. The figures are not real. There is another column with header Second Year. I will add figures for every region.
The program (macro) checks if the figure in the Second year is less than the First year. If the amount is less, I want the macro to add a link next to the figure and add another link in Sheet2 linking to the figure in Sheet1 (with less amount).
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 1 Then Exit Sub ' IF ITS A HEADER, DO NOTHING. If Trim(Target.Text) <> "" Then addHyperLink End If End Sub Sub addHyperLink() On Error GoTo ErrHandler Application.ScreenUpdating = False Dim myDataRng As Range Dim cell As Range Set myDataRng = Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row) Application.Worksheets("Sheet2").Columns(1).ClearContents Application.Worksheets("Sheet2").Cells(1, 1) = "Critical Log" For Each cell In myDataRng If cell(myDataRng.Row, 1).Text < Val(cell(myDataRng.Row, 0).Text) Then ' ADD ADDRESS. Application.ActiveSheet.Hyperlinks.Add _ Anchor:=Application.ActiveSheet.Cells(cell.Row, cell.Column + 1), _ Address:="mailto:email@example.com?subject=Sales Report", _ SubAddress:="", _ ScreenTip:="Critical", _ TextToDisplay:="Mail this Figure" ' ADD SUB-ADDRESS Application.Worksheets("Sheet2").Hyperlinks.Add _ Anchor:=Application.Worksheets("Sheet2").Cells(cell.Row, 1), _ Address:="", _ SubAddress:=Application.ActiveSheet.Name & "!" & cell.Address, _ ScreenTip:="Critical", _ TextToDisplay:="Check Figure" End If Next cell Set myDataRng = Nothing ErrHandler: Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Copy or Move Files from One Folder to Another using VBA (Popular Post)
The first method will add a Hyperlink (in the same sheet) if the entered amount is less than the previous year’s amount.
The second method adds a Hyperlink in Sheet2 (first column). It does have an Address, but a SubAddress linking back to the cell with less amount in Sheet1. (See picture above)
I have used this method twice in my example. Therefore, it is important that I explain the method and its properties.
The Hyperlinks.Add() method will add a hyperlink to a specified range and it takes five parameters.
Add(Anchor As Object, Address As String, [SubAddress], [ScreenTip], [TextToDisplay]) as Object
The first two parameters are required and the rest are optional.
Anchor (required) – An excel Range to add the anchor for the Hyperlink
Address (required) – The address value of the Hyperlink
SubAddress (optional)– SubAddress property of the Hyperlink
ScreenTip (optional) – Looks similar to a Tooltip. Shows a little message when we hover the mouse over the Hyperlinks.
TextToDisplay (optional) – The Hyperlink label showing an underlined text for users to click the link
Well, now you know how to add Hyperlinks to your Excel sheet, dynamically. This little piece of code will spare you from adding hyperlinks manually to each cell and in the process save precious time. You do not have to worry about missing links anymore.
Thanks for reading.