Microsoft Excel - Dropdown List of Hyperlinks

Question

I have a collection of all the different part numbers for different versions of a single part, and each of them is hyperlinked to their specific different drawing in pdf form. I would like to put them into a Data Validation list so that someone can select which version they would like, however the hyperlinks do not "go with" the cells when they are selected (only does plain text). How can I get the hyperlinks to carry over for each different item in the list?

Edit: I made a small table with each part number and the corresponding link to the file server location in plain text. Then I can have a dropdown that selects any of the parts, and a second cell that uses that selection to generate the correct hyperlink from the table. The issue is this solution requires 2 different cells, where I only have room for one. Is there a way to get my dropdown list to also change hyperlinks in-cell?

Do you mind illustrating your problem with some screenshots? I have difficulties understanding the question. Also, note that StackOverflow is a site for programmers. Here you can find a list of things which are on topic and which are off-topic for this site. While I do not fully comprehend your question yet (as stated above) it would seem to me that your question might be off-topic for this site and better suited for another forum. — Ralph
I also only know to do it with two cells. Other than that, you may have to use VBA and the Worksheet_Change event — Sun

elliot svensson elliot svensson · Accepted Answer · 2018-03-15T14:45:13

To accomplish it in one cell, you use the Worksheet_Change event.

This is how to get to the Worksheet change event in VBA:

I wrote a simple code that does the trick for me:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
If Left(Target.Value, 4) = "http" Then
On Error GoTo 0

On Error Resume Next
Set result = Hyperlinks.Add(Target, Target.Value)
On Error GoTo 0

On Error Resume Next
End If
On Error GoTo 0

End Sub

(This little program has "on error resume next" and "on error goto 0" everywhere because it will run a zillion times, whenever anybody does anything on the sheet. I didn't want "on error resume next" staying alive any longer than it absolutely has to.)

Good luck!

Microsoft Excel - Dropdown List of Hyperlinks

1 Answers