I've got several hyperlinks in a worksheet, and I would like to run some VBA
code in the FollowHyperlink event of the worksheet. However, I would like
to use a series of "If" statements that test to see which of the several
hyperlinks was clicked, so I can set different lines of code to correspond
to the different links.

How do I test to see which of several different hyperlinks was clicked in a
worksheet.

(Tom or Dave - can you help me with this one too?)

Thanks in advance,

Paul

In the followhyperlink event, the event argument Target returns a hyperlink
object reference to the hyperlink that triggered the event.

target.Range.Address would return the address of the cell that contained
the hyperlink

target.address and target.subaddress would return those parts of the
hyperlink.

for testing tryt this

Private Sub FollowHyperlink( target as Hyperlink)

msgbox target.Range.Address & vbnewline _
& target.Address & vbNewLine & target.subAddress

End sub

if the hyperlink is to other pages /ranges, then target.address will
probably be empty.

--
Regards,
Tom Ogilvy

Paul James wrote in message
...
I've got several hyperlinks in a worksheet, and I would like to run some
VBA
code in the FollowHyperlink event of the worksheet. However, I would like
to use a series of "If" statements that test to see which of the several
hyperlinks was clicked, so I can set different lines of code to correspond
to the different links.

How do I test to see which of several different hyperlinks was clicked in
a
worksheet.

(Tom or Dave - can you help me with this one too?)

Thanks in advance,

Paul

Great! Thank you (again), Tom.

I got it to work by tweaking the syntax in the first line:

Private Sub worksheet_FollowHyperlink(ByVal target As Hyperlink)
MsgBox "yes"
MsgBox _
target.Range.Address & vbNewLine _
& target.Address & vbNewLine _
& target.SubAddress

End Sub

Thanks again, Tom.

Paul

FYI:

Target.Range.Address returns the address where the hyperlink itself is
located.

Target.SubAddress returns the address of the hyperlink's target
(destination).

You should always select an event declaration from the dropdown - that would
have avoided any declaration problems. The computer I was posting from only
has Excel 97 (no followhyperlink event), and I don't have the declarations
of all events memorized - so apologize if that caused you a problem. I am
aware of what the msgbox displays. You said you needed to identify what
hyperlink is firing - so I gave you several things to choose from as an
example.

--
Regards,
Tom Ogivy

Paul James wrote in message
...
I got it to work by tweaking the syntax in the first line:

Private Sub worksheet_FollowHyperlink(ByVal target As Hyperlink)
MsgBox "yes"
MsgBox _
target.Range.Address & vbNewLine _
& target.Address & vbNewLine _
& target.SubAddress

End Sub

Thanks again, Tom.

Paul

Tom, please know that I wasn't commenting on my observations for your
benefit. You've enlightened me on a number of occasions in these newsgroups
with your explainations when I had a question, and you're operating at
several levels of understanding beyond me. I just noticed a few things
about the followHyperlink event which, while probably obvious to you, were
revelations to me. I shared those observations with the group for the
benefit of others who may be at my level of understanding.

So, thank you again for showing me how to get it done.

Paul