links initiated by cell data

J

JB

I would like to add a dropdown box (either a combo box or cell validation)
and make it so that each option is connected to a hyperlink. I know how to
create the dropdown, I'm just not sure how to connect the options to
links.....is this possible? Thank you very much for any information you can
provide.
 
J

JB

Gary''s Student said:
Start with a table in M1 thru N5:

1 http://www.cnn.com
2 http://www.abc.com
3 http://www.nbc.com
4 http://www.cbs.com
5 http://www.fox.com

In A1 put your data validation dropdown covering the list in column M
In B1 put:

=HYPERLINK(VLOOKUP(A1,M1:N5,2,FALSE))

The VLOOKUP picks the right link and the HYPERLINK function makes the result
"click-able". Enjoy.
Click to expand...
Thank you sooo much!!

Not to be a huge pain, but is there a way for the link that results from the
vlookup function to activate automatically?
(I just figured I'd ask :))
 
G

Gary''s Student

Yes it can be done.

We used a Worksheet Change Event Macro. The macro activates when the value
in A1 changes, the macro gets the link from B1 and Activates it. Assuming
that you are not shy about VBA, I will update this post tomorow.
 
G

Gary''s Student

Keep the original approach, so the link appears in B1. Put the following in
worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then
Exit Sub
End If
Dim s As String
s = Range("B1").Value
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

REMEMBER: worksheet code, not a standard module.
 
J

JB

Thank you, but when i put in the code and tested it, it gave me a run-time
error '5': Invalid procedure or argument :( (any suggestions?)
 
J

JB

Nevermind! I made an error on the target cell, I just fixed it and now it
works perfectly!!!! YOU ARE AWESOME!!!
 
G

Gary''s Student

You are welcome ! I think hyperlinks are under-utilized. As you can see,
they are great for automating tasks.
 
S

Shelly

This is very similar to something I want to do, and I'm hoping you help me too!

I want to use Data Validation, with a list, but I want the actual value to
be "hyperlinked" without the actual link showing.

So, I'd want the list of networks to appear in the drop-down list (CNN, ABC,
NBC, etc.) then when the user selects one of them, the name is what is in the
cell, but it's got a hyperlink associated with it. So they can then click
right on the cell where the drop down was.

Thanks!!
 
M

msnyc07

Hi Gary, not the OP here but I tried this solution for navigating through
multiple worksheets in a workbook. Idea was to have a Frozen Pane on top
with drop-down navigation of workbook, and I had the text display for all the
hyperlinks read 'Go'.

The N Column Hyperlinks work fine, however when I use them in B they say
'Can't Find Specified File'. Do I need some 'absolute' reference?

FYI the Hyperlinks are to named ranges not specific cells, I can't see how
that would matter though...
 
Top