Drop Down w/Hyperlinks

[Livin404]

I have no problem creating hyperlinks in Drop Downs. I have numerous links and I want to change “Text to display”, since the links can be quite long. I tried the standard right click then clicked on Hyperlink, but when I change it on my list and I have a validation list, the link no longer works. Any suggestions?

Thank you!

 

[Yongle]

One way - using minimal VBA

Create a list of short names and associated links
Dropdown list source is short names
Short Name is looked up to get link
VBA follows that link

In my example - everything is in one sheet & changing value in A2 takes user to the selected link link

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

Dropdown​

[/td][td]

Lookup result​

[/td][td][/td][td=bgcolor:#C6E0B4]ShortName[/td][td=bgcolor:#C6E0B4]Link [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#FFFF00]

abc file​

[/td][td]

C:\Test\abc.xlsm​

[/td][td][/td][td=bgcolor:#D9E1F2]gooogle[/td][td=bgcolor:#D9E1F2]http://www.google.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#D9E1F2]wiki[/td][td=bgcolor:#D9E1F2]https://en.wikipedia.org/wiki/Main_Page[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#D9E1F2]abc file[/td][td=bgcolor:#D9E1F2]C:\Test\abc.xlsm[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


Dropdown in A2 with DataValidation list source column D
Formula in B2 =LOOKUP(A2,D:E,2,0)

VBA which is pasted into SHEET module
(right-click sheet tab \ select View Code \ paste code in code window \ {ALT}{F11} to go back to Excel)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2"), Target) Is Nothing Then
        On Error Resume Next
        ActiveWorkbook.FollowHyperlink Address:=Range("B2")
    End If
End Sub

Helper cell B2 is not required - could use this instead and allow VBA to do the LookUp

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2"), Target) Is Nothing Then
        Dim L As String
        On Error Resume Next
        L = WorksheetFunction.VLookup(Target, Range("D:E"), 2, 0)
        ActiveWorkbook.FollowHyperlink Address:=L
    End If
End Sub

 

[Swayzy]

Have you tried the normal formula hyperlink function? You can use a formula in the hyperlink function in the text to display argument, so you dont need vba. And you can hade One single dropdown menu with some index match to fetch right Link and name

 

[Yongle]

As @Swayzy says, there are alternatives to using VBA
- it is easy to adapt example data in post#2 with a different formula in B2

Formula required in B2
=HYPERLINK(VLOOKUP(A2,D:E,2,0),A2 & " (or select in A2)")

VBA solution
- hyperlink is triggered by selecting from dropdown
Non-VBA
- user selects from dropdown in A2 and hyperlink is triggered when user clicks on link in B2