How to get Cell.Address from Hyperlink cell

[Rick S.]

I can get the cell address from a named cell easily enough, but I can not
seem to figure how to get the cell address of a hyperlinked cell that has
been clicked.

All help is appreciated.

'======
'SS_22 (source)
'FF_S_86.Show (target)
On Error Resume Next
sS = Target.Name.Name 'get parent name of cell, target = "FF_S_86.Show"
On Error GoTo 0
If sS = "SS_22" Then 'Named Cell
Call x 'call routine to save variable in registry
FF_S_86.Show
sS = "" 'set sS to nothing
Else
sS = "" 'set sS to nothing
End If
'======
Registry Routine:
'======
Sub x()
'Begin Registry setting
sSpecLastWs = ActiveSheet.Name
SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
Key:="sSpecLastWs", setting:=sSpecLastWs
sSpecLastCell = ActiveCell.Address
SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
Key:="sSpecLastCell", setting:=sSpecLastCell
'End Registry setting
End Sub
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Juan Pablo González]

Have you tried using the _FollowHyperlink event?

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox Target.Range.Address
End Sub

Regards,

Juan Pablo González

 

[Joel]

Range("B8").Hyperlinks(1).Name

 

[Rick S.]

This is starting to scare me, I must be learning.
This appears to work.

'======
On Error Resume Next
sS2 = ActiveCell.Name.Name 'Get cell Name not address
With sS2
If sS2 = "SS_33" Then
If .Hyperlinks.Count > 0 Then
sSpecLastCell = ActiveCell.Address
SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
Key:="sSpecLastCell", setting:=sSpecLastCell
End If
End If
sS2 = ""
End With
'======

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Joel]

The trick I used to get the answer is simple

I did
set a = Range("B8")

Then I added a to the watch window in VBA. I seted through the code unitl a
was set. Then looked at variable a to determine the correct format of the
statement. The address was under Item 1 in Hyperlinks as a name.

Wait until you look at variables inside the watch window. You'll really get
scared.

 

[Rick S.]

If I knew how to use the Watch Window, other than telling the time. LOL
(I kill me) :BG
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Joel]

Either highlight variable in VBA window and right click mouse - add to watch.
Or under Debug menu in VBA - Add Watch.

 

[Rick S.]

Thanks!
This is much cleaner and easier (I am working with named cells).
'======
'SS_20
sS2 = Target.Range.Name.Name
'MsgBox sS2
If sS2 = "SS_20" Then
Call x2
End If
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007