How do you discover the caller in a sheet pointed to by a hyperlink?

If I try Application.Caller in the sheet's event Worksheet_Activate I get errors. I'm guessing that I'll need something more elaborate if I need to discover the link that called it.

Thank you

How do you discover the caller in a sheet pointed to by a hyperlink?

If I try Application.Caller in the sheet's event Worksheet_Activate I
get errors. I'm guessing that I'll need something more elaborate if
I need to discover the link that called it.

Thank you

Perhaps this event...

Worksheet_FollowHyperlink

...but there may be a better way to handle whatever it is your trying to
do. ¤Can you elaborate?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Perhaps this event...



Worksheet_FollowHyperlink



..but there may be a better way to handle whatever it is your trying to

do. EURO Can you elaborate?

Tried that, but no go.
I have a dynamic report that is updated based on an employee selection. If you are on the report sheet, this selection is made via a combobox. It is also possible to select an employee from a roster located on another sheet.. Right now I use a hyperlink. I need to identify the caller so I can regenerate the report. It doesn't look like the hyper link will make it.

hi,

i'm not sure to well understanding,
the Hyperlink is it into a microsoft word object (document) placed in a worksheet ?

isabelle

Le 2014-02-21 18:45, a écrit :
How do you discover the caller in a sheet pointed to by a hyperlink?

If I try Application.Caller in the sheet's event Worksheet_Activate I get errors.
I'm guessing that I'll need something more elaborate if I need to discover the link that called it.

Thank you

On Saturday, February 22, 2014 12:15:36 AM UTC-6, isabelle wrote:
hi,



i'm not sure to well understanding,

the Hyperlink is it into a microsoft word object (document) placed in a worksheet ?


Excel calls it a hyperlink but the option is set to a "place in this document". If you edit it, doesn't look like "hyperlink" construct. Not in the mode "place in this document" anyway.

ok

'in ThisWorkbook

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As
Hyperlink)
MsgBox "From : " & Sh.Name & " " & Target.Parent.Address & " to : " &
Target.SubAddress
End Sub

isabelle

Le 2014-02-22 01:34, a écrit :

Excel calls it a hyperlink but the option is set to a "place in this document".
If you edit it, doesn't look like "hyperlink" construct. Not in the mode "place in this document" anyway.

or with tab name and address separately

on ThisWorkbook

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As
Hyperlink)
MsgBox "From : " & Sh.Name & " " & Target.Parent.Address(0, 0) & Chr(10) &
Chr(10) & " To : " & Split(Target.SubAddress, "!")(0) & " " &
Split(Target.SubAddress, "!")(1)
End Sub

isabelle

also if you want to use variables in a standard module

'on ThisWorkbook
'------------------------------------------------------------------------------------------------------------------
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As
Hyperlink)
fromSheet = Sh.Name
fromRange = Target.Parent.Address(0, 0)
toSheet = Split(Target.SubAddress, "!")(0)
toRange = Split(Target.SubAddress, "!")(1)
Macro1
End Sub
'------------------------------------------------------------------------------------------------------------------


'on Module
'------------------------------------------------------------------------------------------------------------------
Public fromSheet As String
Public fromRange As String
Public toSheet As String
Public toRange As String


Sub Macro1()
MsgBox "From : " & fromSheet & Chr(10) & _
"range : " & fromRange & Chr(10) & _
Chr(13) & Chr(13) & _
"To : " & toSheet & Chr(10) & _
"range : " & toRange
End Sub
'------------------------------------------------------------------------------------------------------------------

isabelle

Perhaps this event...



Worksheet_FollowHyperlink



..but there may be a better way to handle whatever it is your trying
to

do. EURO Can you elaborate?

Tried that, but no go.
I have a dynamic report that is updated based on an employee
selection. If you are on the report sheet, this selection is made
via a combobox. It is also possible to select an employee from a
roster located on another sheet. Right now I use a hyperlink. I
need to identify the caller so I can regenerate the report. It
doesn't look like the hyper link will make it.

If you impliment using a local scope defined name on the report sheet,
you can update that when the respective name on the roster list is
clicked. Then your report generator can read the name and carry on...

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Sheets("Report").Names("CallerID").RefersTo = _
"=""" & Target.Parent & """"
End Sub

...where the defined name is initialized with an empty string when
created, and your report generator restores its value to an empty
string after doing its thing.

You can also have your combobox use this same mechanism so your report
generator code needs only to retrieve the name stored in 'CallerID'.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

the events were off... I apologize.

the events were off... I apologize.

Please explain...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion