Hyperlink screentip (VBA)

[justinjam]

Hello All,
I need some advice from Excel Guru's who know how to resolve the current challange am having (Excel 2007). The procedure is to have a dynamic screentip from Hyperlink - BUT the screeptip text is not updated herein and only show static initial value. I have included the code to illustrate what I have tried so far.

Private Sub Worksheet_FollowHyperlink(ByVal target As Hyperlink)
'OK :)
    If Me.Name = "frmPersonnel" And target.Range.Address = "$N$5" Then
        If target.TextToDisplay = "Emp.B ID" Then
                target.TextToDisplay = "Emp.A ID"
                target.Range.Offset(1, 0).Value = Me.Range("EmpID_A")
                'Target.Range.Offset(1, 0).Value = Target.Range.Offset(2, 1).Value  '//empty value
                target.ScreenTip = "Click to display ID from Employer B"
        Else
                target.TextToDisplay = "Emp.B ID"
                target.Range.Offset(1, 0).Value = Me.Range("EmpID_B")
                'Target.Range.Offset(1, 0).Value = Target.Range.Offset(2, 0).Value
                target.ScreenTip = "Click to display ID from Employer A"
        End If
    End If
End Sub