Another sheet cell value is populated with the value of hyperlink clicked

Hi,

I am trying to write a code where if a user clicks on a range of hyperlinks, another sheet value gets changed to the value of the hyperlink clicked rather than the hyperlink (i believe for this the hyperlink will have to refer to itself). I have a code already which i wrote for to execute on cell selection, but i figured that the report will be more smooth if it is based on hyperlink clicking rather than cell selection criteria (creates problem with data entry):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    If Selection.Count = 1 Then
     'Put in your actual range and the cell where you the text to be shown
        If Not Intersect(Target, Sheets("Assets").Range("C2:C3500")) Is Nothing Then
            Selection.Copy
            Sheets("Asset report").Range("F2").PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            Worksheets("Asset Report").Activate
      End If
    End If


     If Selection.Count = 1 Then
     'Put in your actual range and the cell where you the text to be shown
        If Not Intersect(Target, Sheets("Assets").Range("S2:S3500")) Is Nothing Then
            Selection.Copy
            Sheets("Asset report").Range("F2").PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            Worksheets("Asset Report").Activate
 
   End If
  End If
End Sub

If anyone can let me how to modify the code so that i can achieve my desired result.

Quote from royUK

A WorkSheet has a FollowHyperlink event. Maybe try that.

I've tidied your code, but not tested it.

Code

Option Explicit


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With Target
''/// presumably Sheets("Assets") is the active sheet so no need to define it
If .Count > 1 Then GoTo exit_proc
'Put in your actual range and the cell where you the text to be shown
If Not Intersect(Target, Me.Range("C2:C3500")) Is Nothing Then
Sheets("Asset report").Range("F2") = .Value
''/// why activate this sheet?
Worksheets("Asset Report").Activate
End If


If .Count > 1 Then GoTo exit_proc
'Put in your actual range and the cell where you the text to be shown
If Not Intersect(Target, Me.Range("S2:S3500")) Is Nothing Then
Sheets("Asset report").Range("F2") = .Value
Worksheets("Asset Report").Activate
End If
End With
exit_proc:
MsgBox "Please select only one cell", vbCritical, "Selection error"
End Sub

Display More

Thank a lot. Just to let you know why i activated the above sheet. The value in F2 is used to perform certain calculations in the sheet and if the person wants to know those calculations on individual level, he will click on the that cell where he will be then redirected to that sheet and the exit_proc doesnt seem to be working.