Hyperlinks in drop-down list

[cdb]

Hi,

I'm trying to create a table of contents using a drop down list showin
the various worksheets I have in a workbook, and when you click on on
of the worksheet titles it takes you there. Hyperlinks are easy, dro
down lists are easy, now how do I mix the 2 together?

(I realize when you right-click on the VCR controls Excel does this fo
you, but most of the people who will use this document don't know that
It's more for useability and cleanliness).

Thanks in advance

 

[John Williams]

Hi,

I'm trying to create a table of contents using a drop down list showing
the various worksheets I have in a workbook, and when you click on one
of the worksheet titles it takes you there. Hyperlinks are easy, drop
down lists are easy, now how do I mix the 2 together?

(I realize when you right-click on the VCR controls Excel does this for
you, but most of the people who will use this document don't know that.
It's more for useability and cleanliness).

There's no need to use hyperlinks. You just need to trap the ComboBox
change event and activate the selected worksheet. (BTW, what do you
mean by VCR controls?)

I've implemented your question using a VBA userform and module macro.

First, in the VBA IDE, create a UserForm (UserForm1) containing a
ComboBox (ComboBox1) and a CommandButton. The command button is just
for closing the form.

Add the following code to UserForm1:

Private Sub ComboBox1_Change()
Worksheets(ComboBox1.List(ComboBox1.ListIndex)).Activate
End Sub

Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

------

Next, add a Module and define the following macro:

Sub SelectSheet()
'Populate the Combobox with the worksheets in this workbook

Dim i As Integer

For i = 1 To Worksheets.Count
UserForm1.ComboBox1.AddItem Worksheets(i).Name
Next

UserForm1.ComboBox1.ListIndex = 0
UserForm1.Show

End Sub

----------

Run the SelectSheet macro to display the form, and select any sheet in
the workbook from the combobox. Click the command button or X close
icon to close the form.

I hope that helps.