Drop Down w/Hyperlinks

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
774
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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)
Code:
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
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,889
Messages
6,181,606
Members
453,055
Latest member
cope7895

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top