Create Hyperlink to a Specific Value Instead of the Cell Itself

[shauste]

Hello,

I would like a way to create a hyperlink to a specific value in a cell instead of the cell itself. As an example, Cell C11 might say RFP-WM-010 but when new rows are inserted C11 might not always correspond with RFP-WM-010. I want a way to link to specific value instead of the cell itself. Is that possible?

 

[John_w]

See if this event handler works for you. The only proviso is that the hyperlink's Text to Display must be the same as the cell value that it links to. Following your example, suppose the hyperlink is in cell A2, its Text to Display must be RFP-WM-010 and it links to cell C11 on the same sheet.

For test purposes only, the following macro creates the example hyperlink:

Sub Create_Hyperlink()
    With ActiveSheet
        .Range("C11").Value = "RFP-WM-010"
        .Hyperlinks.Add Anchor:=.Range("A2"), Address:="", SubAddress:=.Name & "!C11", TextToDisplay:="RFP-WM-010"
    End With
End Sub

Here is the event handler. This code must be put in the worksheet module of the sheet containing the hyperlink(s). See https://www.contextures.com/xlvba01.html#Worksheet if you're not sure about this.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim subAddressParts As Variant
    Dim targetColumn As Long
    Dim findRow As Variant
    
    subAddressParts = Split(Target.SubAddress, "!")
    targetColumn = Range(subAddressParts(1)).Column
    
    findRow = Application.Match(Target.TextToDisplay, Me.Columns(targetColumn))
    If Not IsError(findRow) Then
        If findRow <> Range(subAddressParts(1)).Row Then
            'The row has changed so change the hyperlink's subAddress and follow it
            Target.SubAddress = subAddressParts(0) & "!" & Cells(findRow, targetColumn).Address(False, False)
            Application.EnableEvents = False
            Target.Follow
            Application.EnableEvents = True
        End If
    Else
        MsgBox "Hyperlink text '" & Target.TextToDisplay & "' not found in column " & Split(Cells(1, targetColumn).Address(True, False), "$")(0)
    End If
    
End Sub

Save the workbook as a macro-enabled workbook (.xlsm), close and reopen. Test by inserting or deleting rows.

 

[shauste]

Before I try this...which I will have to follow the other link you provided...what if I need to link to another sheet?

 

[John_w]

The Worksheet_FollowHyperlink event handler should work with a hyperlink to another sheet, however I haven't tested that scenario.

 

[John_w]

I tried the code with a hyperlink to another sheet, however it doesn't work because it's trying to find the Text to Display on the same sheet as the hyperlink, and displays the MsgBox warning.

EDIT - I've got it working with a hyperlink to another sheet.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim subAddressParts As Variant
    Dim targetColumn As Long
    Dim findRow As Variant
    
    subAddressParts = Split(Target.SubAddress, "!")
    targetColumn = Range(subAddressParts(1)).Column
    
    findRow = Application.Match(Target.TextToDisplay, Worksheets(subAddressParts(0)).Columns(targetColumn))
    If Not IsError(findRow) Then
        If findRow <> Range(subAddressParts(1)).Row Then
            'The row has changed so change the hyperlink's subAddress and select the target cell
            Target.SubAddress = subAddressParts(0) & "!" & Cells(findRow, targetColumn).Address(False, False)
            Application.EnableEvents = False
            Worksheets(subAddressParts(0)).Activate
            Worksheets(subAddressParts(0)).Cells(findRow, targetColumn).Select
            Application.EnableEvents = True
        End If
    Else
        MsgBox "Hyperlink text '" & Target.TextToDisplay & "'  not found in column " & Split(Cells(1, targetColumn).Address(True,  False), "$")(0)
    End If
    
End Sub

 

[shauste]

So, this should regardless of whether its the same sheet or another sheet? Each sheet is prefixed with AM Asset Mgmt, MM Materials Mgmt, or WM Work Mgmt. So a row on WM might be referenced on MM or AM and vice versa.

 

[shauste]

When I inserted the code, then inserted the link, and when I clicked on the link I received an error (Run-time error '9' Subscript out of range) that pointed to a highlighted line.

findRow = Application.Match(Target.TextToDisplay, Worksheets(subAddressParts(0)).Columns(targetColumn))

 

[John_w]

So, this should regardless of whether its the same sheet or another sheet? Each sheet is prefixed with AM Asset Mgmt, MM Materials Mgmt, or WM Work Mgmt. So a row on WM might be referenced on MM or AM and vice versa.

Yes, it should work with the same sheet or another sheet.

To cater for spaces (or no spaces) in the sheet names, and this should fix the run-time error, use this code instead:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim subAddressParts As Variant
    Dim targetColumn As Long
    Dim findRow As Variant
    
    subAddressParts = Split(Target.SubAddress, "!")
    subAddressParts(0) = Replace(subAddressParts(0), "'", "")
    targetColumn = Range(subAddressParts(1)).Column
    
    findRow = Application.Match(Target.TextToDisplay, Worksheets(subAddressParts(0)).Columns(targetColumn))
    If Not IsError(findRow) Then
        If findRow <> Range(subAddressParts(1)).Row Then
            'The row has changed so change the hyperlink's subAddress and select the target cell
            Target.SubAddress = "'" & subAddressParts(0) & "'!" & Cells(findRow, targetColumn).Address(False, False)
            Application.EnableEvents = False
            Worksheets(subAddressParts(0)).Activate
            Worksheets(subAddressParts(0)).Cells(findRow, targetColumn).Select
            Application.EnableEvents = True
        End If
    Else
        MsgBox "Hyperlink text '" & Target.TextToDisplay & "' not found in column " & Split(Cells(1, targetColumn).Address(True, False), "$")(0) & " of worksheet " & subAddressParts(0)
    End If
    
End Sub

 

[shauste]

There were no errors but if I change the sort order of column C, for example, then they code doesn't follow value.

 

[Jaafar Tribak]

Hello,

I would like a way to create a hyperlink to a specific value in a cell instead of the cell itself. As an example, Cell C11 might say RFP-WM-010 but when new rows are inserted C11 might not always correspond with RFP-WM-010. I want a way to link to specific value instead of the cell itself. Is that possible?

Have you considered hyperlinking to a named range ?

If you give Cell C11 a range name then no matter where C11 is moved , the hyperlink should always point to the new location of C11 and consequently, clicking the hyperlink should always take you to the value RFP-WM-010