Worksheet_FollowHyperlink - get the cell value in

Question

I have a value in a cell and when I double click on it it takes me to the Named Range Account_Number (which resides on another worksheet) and updates the value.

My problem is I would like to adapt my code below so it will work with the Worksheet_FollowHyperlink(ByVal Target As Hyperlink) event.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If (ActiveCell.Column = 23 And Not ActiveCell.Value = "") Then   
    [Account_Number] = ActiveCell.Value
    Application.GoTo Reference:=[Account_Number]
End If

End Sub

I would like to put a hyperlink for instance in cell J9 which contains the value 4111 and when I click on the hyperlink it would take me to the Named Range in the other worksheet and update the value of the Named Range to 4111.

I am uncertain how to dynamically assign the value to the Named Range. Can someone please let me know if this is possible and what the code should be?

Thank you

Answer 1

If you have made a hyperlink to a named cell, the way to copy the value from hyperlink source cell to its target would be:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    ActiveCell.Value = Target.Parent.Value
End Sub

You might want to apply this only to hyperlinks to particular named cell, like:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  If Target.SubAddress = "Account_Number" Then
    ActiveCell.Value = Target.Parent.Value
  End If
End Sub