Insert sheet name hyperlinks in form control combo list box

Question

I have an excel workbook in which sheets are created on the basis of the values in a dynamic name range with same name as the values in the dynamic name range.

Now i am trying to create a form control combo list box ,with list of all the dynamically created sheet name such that they list options are hyper linked to their respective sheets.

My doubts are :

1) how do i create a combo box with hyperlinks

2) how to i get sheet names for the hyperlinks.

For 2) I think it would be easy as for the combo box i can give ListFillRange as the dynamic range with list of sheet names. But how do i hyperlink these names to their respective sheets?

Please provide more information on how you would like a combo box to be hyperlinked. Do you want the items to be blue, underlined, and go directly to the sheet when clicked? And just checking, by "combo box" do you mean a drop-down list? — Aaron Thomas

Archlight Archlight · Accepted Answer · 2014-03-04T08:42:35

you cant add hyperlinks to a dropdown. But it's trivial to get the same functionality through other methods.

This adds all sheets that contain bla in their name ( your dynamic sheet names ) and then lists them as a dropdown and when the dropdown changes the sheet that you choose gets activated.

Option Compare Text
Sub test()
    ComboBox1.Clear
    For Each Sheet In Sheets
        If InStr(Sheet.Name, "bla") > 0 Then
            ComboBox1.AddItem (Sheet.Name)
        End If
    Next
End Sub
Private Sub ComboBox1_Change()
    If ComboBox1.Text <> "" Then
        Sheets(ComboBox1.Text).Select
    End If
End Sub

Insert sheet name hyperlinks in form control combo list box

1 Answers