Combo box and hyperlinks

**reddwarf** · 10-09-2009, 04:02 PM

Anyone got an example of how to use a combo box to select hyperlinks to take you off to the selected website? I have this, which works but it's hard-coded - I'm looking for something a little more dynamic, i.e. pick up hyperlinks from a range on a sheet.

   Dim strAddress As String
    Select Case intIndex
    Case 0
        strAddress = "http://www.reverso.net/text_translation.asp?lang=en"
    Case 1
        strAddress = "http://translate.google.com/translate_t?"
    Case 2
        strAddress = "http://www.wordreference.com/"
        
    End Select
    
     ThisWorkbook.FollowHyperlink strAddress, , True

**Leith Ross** · 10-09-2009, 10:47 PM

Hello reddwarf,

The attached workbook contains a list of hyperlinks that are loaded into a ComboBox on worksheet "Sheet1". The font color is blue and underlined to signal the entries are hyperlinks. When user clicks on a selection, the link is opened in the web browser. Here are the macros that are used.

Workbook Open Event

Private Sub Workbook_Open()
  LoadHyperlinks
End Sub

Worksheet Sheet1 Code

Private Sub ComboBox1_Click()

  Dim Hlnk As Hyperlink
  
    If ComboBox1.ListIndex <> -1 Then
       Set Hlnk = Evaluate(ComboBox1.List(ComboBox1.ListIndex, 1)).Hyperlinks(1)
       Hlnk.Follow
    End If
    
End Sub

Module1 Code to Load the ComboBox

Sub LoadHyperlinks()

  Dim Hlnk As Hyperlink
  Dim Rng As Range
  
    Set Rng = Worksheets("Sheet1").Range("A2:A4")
    
    With Worksheets("Sheet1").ComboBox1
      .Clear
        For Each Hlnk In Rng.Hyperlinks
          .AddItem
          .List(.ListCount - 1, 0) = Hlnk.TextToDisplay
          .List(.ListCount - 1, 1) = "'" & Hlnk.Range.Parent.Name & "'!" & Hlnk.Range.Address
        Next Hlnk
      .Text = "--- Select a Site ---"
    End With
    
End Sub