hyperlink - place in this document - can discover caller in target sheet?

  • Thread starter Thread starter oldyork90
  • Start date Start date
O

oldyork90

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
Click to expand...

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?
Click to expand...

Tried that, but no go.
I have a dynamic report that is updated based on an employee selection. Ifyou are on the report sheet, this selection is made via a combobox. It isalso 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, (e-mail address removed) a écrit :
 
hi,



i'm not sure to well understanding,

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

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, (e-mail address removed) a écrit :
 
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...
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.
Click to expand...

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
 
Back
Top