Dymanic Hyperlink based on cells value

johnyc · Jun 30th 2016

Hi,

I have a specific table with the master data of the products. The table contains product ID and description. In another sheet the user enter random product IDs. For each product ID he enters I want to automatically convert the cell to hyperlink to the correct cell of the product table.

I use the Worksheet_Change function to identify the range of cells that a user changes but with no success.

Code

Private Sub Worksheet_Change(ByVal Target As Range)


    If Not Intersect(Target, Range("A7:A10")) Is Nothing Then    
        Dim r As Range
        For Each r In Target.Cells
            Range(r.Address).Value = _
             "=HYPERLINK(""#Products!""&ADDRESS(MATCH(" & r.Address & ";Products!$A:$A;0);1);" & r.Address & ")"
        Next
    End If
End Sub

Display More

**Ger Plante** · Jul 1st 2016

Re: Dymanic Hyperlink based on cells value

You had approximately the right idea.

This worked for me with my product codes in column A of the products Sheet, and entering the product ID in another sheet in range A7:A10

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("A7:A10")) Is Nothing Then
        Dim r As Range
        For Each r In Target.Cells
            r.Hyperlinks.Delete 'delete old hyperlink in case new product ID doesnt exist.
            r.Hyperlinks.Add r, "", "Products!$A$" & Application.Match(r.Value, Worksheets("Products").Range("$A:$A"), 0)
        Next
    End If
End Sub

johnyc · Jul 1st 2016

Re: Dymanic Hyperlink based on cells value

Thank you! It worked like a charm!

Dymanic Hyperlink based on cells value

Participate now!

Share

Users Viewing This Thread