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?

 

[shauste]

Jaafar, I have not tried that. Let me see if that will work.

 

[John_w]

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

Probably because Application.Match isn't finding the cell value. Try the following code instead. This uses the Range.Find method to find the cell value and works with hyperlinks to a defined name and the normal Sheet!Cell reference.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim subAddressParts As Variant
    Dim destSheetName As String, destColumn As Long, currentRow As Long
    Dim findCell As Range
    
    subAddressParts = Split(Target.SubAddress, "!")
    If UBound(subAddressParts) = 1 Then
        'Link refers to a Sheet!Cell
        destSheetName = Replace(subAddressParts(0), "'", "")
        destColumn = Range(subAddressParts(1)).Column
        currentRow = Range(subAddressParts(1)).Row
    Else
        'Link refers to a defined name
        destSheetName = Application.Range(Target.SubAddress).Worksheet.Name
        destColumn = Application.Range(Target.SubAddress).Column
        currentRow = Application.Range(Target.SubAddress).Row
    End If
    
    With Worksheets(destSheetName)
        Set findCell = .Columns(destColumn).Find(What:=Target.TextToDisplay, After:=.Cells(1, destColumn), _
                        LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    End With
    
    If Not findCell Is Nothing Then
        If findCell.Row <> currentRow Then
            'The row has changed so change the hyperlink's subAddress and select the found cell
            Target.SubAddress = "'" & destSheetName & "'!" & Cells(findCell.Row, destColumn).Address(False, False)
            Application.EnableEvents = False
            findCell.Worksheet.Activate
            findCell.Select
            Application.EnableEvents = True
        End If
    Else
        MsgBox "Hyperlink text '" & Target.TextToDisplay & "' not found in column " & Split(Cells(1, destColumn).Address(True, False), "$")(0) & " of worksheet " & destSheetName
    End If
   
End Sub

Note that another limitation is that the cell value RFP-WM-010, or whatever, can only occur once; if the cell value occurs multiple times then the code will always find the first occurrence and update the hyperlink to that cell.

I tried Jaafar's idea of hyperlinking to a named range (defined name), however because the Refers To cell is an absolute cell address it always refers to that cell and doesn't change when the cell value moves. Do you mean a dynamic named range?

 

[shauste]

I'm still trying to study up on these named range ideas but from what I see, I don't think they will work with what I am trying to do.

So, with your latest code, I add it to each sheet and then do I just add a hyperlink like normal or do I have to do something else special to make it all tie together?

The cell value will only occur once.

 

[John_w]

Put the latest code in the worksheet module of the sheet containing the hyperlinks.

If there is more than one sheet which contains hyperlinks which you want to handle in the same way then you can either put the code in the appropriate worksheet modules, or in the ThisWorkbook module as the Workbook_SheetFollowHyperlink event handler, with a small change to the code to tell it which sheets it should operate on.

 

[shauste]

So, I don't put the same code on all 3 sheets?

 

[John_w]

First try the code in 1 sheet with hyperlinks to see if it works.

Then put the same code in each worksheet module of each sheet which has hyperlinks and which you want to handle in the same way.

The only other thing is to create the hyperlinks in the way I described in post no. 2, i.e. each hyperlink's display text must be the same as the cell contents to which it links.

 

[shauste]

Tested the latest code and it seems to work as required. Thank you.

Do I still use the original cell reference when building the hyperlink?

 

[John_w]

As I said in post no. 12, the code works with hyperlinks to a normal cell reference and with hyperlinks to a defined name cell.

 

[shauste]

Thank you. Just making sure I understood. Thanks for your help.