Hyperlinks in Case Statement

[mgiehm]

Hello. Here is what I'm trying to do. I have created a combo box that shows a list of file names. I would like to have those file names become hyperlinks to the actual files. Through some research I am finding that this is either very difficult to do or near impossible. So my question is this, is it possible to create a Case Statement that would create a hyperlink in the field where the combo box is sending its Linked Cell is? Any help would be greatly appreciated.

 

[Jerry Sullivan]

Hi mgiehm,

You don't need to use the Linked Cell to do what you describe.

Below are some examples that will follow a hyperlink address directly from the selection
of an item in the ActiveX ComboBox.

Private Sub ComboBox1_Change()
    Dim strChoice As String
    With ComboBox1
        strChoice = .List(.ListIndex, 0)
    End With
    With ActiveWorkbook
        Select Case strChoice
            Case "MrExcel" 'Hyperlink to a URL address
                .FollowHyperlink Address:="http://www.mrexcel.com/forum/", _
                    NewWindow:=True
 
            Case "MyFile" 'Hyperlink directly to a filepath
                .FollowHyperlink Address:="C:\TEST\MyFile.pdf"
 
            Case "Follow Text at D2" 'Hyperlink to an indirect address
                                     'D2 is "http://www.google.com"
                    .FollowHyperlink _
                        Address:=Sheets("Sheet1").Range("D2").Value
            Case Else
                MsgBox "No links associated with: " _
                    & strChoice
        End Select
    End With
End Sub

 

[mgiehm]

Jerry,

Thank you so much for your reply. It's exactly what I am looking for. I input in the code changed to my file locations and am getting an error on this line.

strChoice = .List(.ListIndex, 0)</pre>

The error says Object required. My combo box list is populated from a list on a different sheet that I have named LockList if that matters. Once again thanks for your help!!

 

[Jerry Sullivan]

There are two kinds of ComboBoxes: ActiveX and Form objects.
Do you know which type you are using?
The code above is written for an ActiveX ComboBox.

 

[mgiehm]

I'm using an Active X combo box. To get your code to work I had to change that line to point to the linked cell. Once I did that the code worked beautifully. Thank you.