3

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?

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?

  • probably you need to use macros for that, I don't see any way without it. – Máté Juhász Jun 2 '16 at 12:16
0

I have tried something similar to what you are going after. There are unfortunately no excel formulas to return the embedded URL of a cell. The only way to retrieve this is through VBA.

  1. You will have to create an event to capture whenever a cell changes that is in a certain range. That range should cover your data validation cells.

Here is an example to get you started for capturing when a cell changes:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1:C10")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

    ' Display a message when one of the designated cells has been 
    ' changed.
    ' Place your code here.
    MsgBox "Cell " & Target.Address & " has changed."

End If
End Sub

Adjust the range shown here to your own range of course.

Make sure to place this code inside of "ThisWorkbook":

enter image description here

  1. Inside that event gather which cell it was and which cell it is connected to for the hyperlink
  2. Use VBA to tell you which hyperlink is in that cell using a function similar to this:

Sample:

Option Explicit
Function HyperLinkText(rg As Range)
    Dim sFormula As String, S As String
    Dim L As Long
    Dim H As Hyperlink, HS As Hyperlinks

sFormula = rg.Formula
L = InStr(1, sFormula, "HYPERLINK(""", vbBinaryCompare)

If L > 0 Then
    S = Mid(sFormula, L + 11)
    S = Left(S, InStr(S, """") - 1)
Else
    Set HS = rg.Worksheet.Hyperlinks
    For Each H In HS
        If H.Range = rg Then
            S = H.Address
        End If
    Next H
End If

HyperLinkText = S

End Function

I don't know how familiar you are with VBA. If you need additional help with the first parts let me know.

  • Thanks for the help! I can open the VBA editor and add code, but I don't know where inside the code I should indicate which cells to check. I have used MATLAB but thats it. – Jacob Whiteman Jun 3 '16 at 10:49
  • @JacobWhiteman I have added some sample code to help get you started with the beginning parts. Hopefully this gets you some results. Just play around with the first event I showed until you fully understand it and then move to adding your own code. – Eric F Jun 6 '16 at 17:14
  • The first event works perfectly, but I am unsure how to use the 2nd one. What does it do exactly? Should there be a way to use VBA and VLOOKUP and/or INDIRECT to check the text in the cell and match it to the right hyperlink? – Jacob Whiteman Jun 9 '16 at 16:55
  • .Address is where the hyperlink is stored not its value – Eric F Jun 10 '16 at 16:25
  • Where the hyperlink is stored as in a table of all the hyperlinks? – Jacob Whiteman Jun 10 '16 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.