PDA

View Full Version : Solved: Selecting a hyperlink using Data Validation



JimS
10-28-2009, 12:38 PM
I have a Data Validation Field on Sheet2 Cell C3 that uses a list from Sheet1 Cells A2-A11 as its input.

Each Cell on Sheet1, that makes up the Data Validation List, also has their own individual Hyperlink (10 different hyperlinks).

Is there a way to open the appropriate hyperlink when the user makes their selection on Sheet2 C3 using the Data Validation List?

Thanks for any help...

JimS

Bob Phillips
10-28-2009, 01:21 PM
Use a selection change event on the DV cell, and get the list value link by matching the selected DV Value against the list, and do a Follow Hyperlik on the matched cell hyperlink value.

JimS
10-28-2009, 03:03 PM
Sounds good, how do I "get the list value link by matching the selected DV Value against the list, and do a Follow Hyperlik on the matched cell hyperlink value"?

mdmackillop
10-28-2009, 03:40 PM
Try this


'Sheet module
Private Sub Worksheet_Change(ByVal target As Range)
DoFollow target
End Sub

'Standard module
Sub DoFollow(target As Range)
ActiveWorkbook.FollowHyperlink Range("Data").Find(target.Value).Hyperlinks.Item(1).Address
End Sub

JimS
10-29-2009, 05:17 AM
mdmackillop,

Thanks - This is what I'm after "but" (always a but), I would like to call the routine from another Macro, not a Change Event.

I can't figure out the syntax so that it will work when called from another macro.

Can it be modified to work?

Thanks again...

JimS

RolfJ
10-29-2009, 07:05 AM
I won't claim that I fully understand why you would want to do that, but, assuming that the DoFollow routine proposed by mdmackillop works for you, you should be able to call it from another macro simply by using the following statement:

DoFollow {range}

where {range} is intended to denote the cell (i.e. range) you would like to pass along to the DoFollow routine, just like mdmackillop did in the Worksheet_Change routine he proposed.

Hope this helped,
Rolf

JimS
10-29-2009, 08:50 AM
Still won't work.

Can it be set up so that a CommandButton1_Click will run the DoFollow code instead of the Worksheet_Change?

mdmackillop
10-29-2009, 10:49 AM
Yes


DoFollow ActiveCell

JimS
10-29-2009, 11:23 AM
That's what I needed.

Thank you both...