Solved: Selecting a hyperlink using Data Validation

[JimS]

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]

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]

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]

Try this

[VBA]
'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

[/VBA]

[JimS]

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]

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]

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]

Yes

[VBA]
DoFollow ActiveCell
[/VBA]

[JimS]

That's what I needed.

Thank you both...