Listbox using List of Hyperlinks to other sheets

[sswcharlie]

Hi

Have a listbox that refers to Named List and works ok. But the list is of hyperlinks to other sheeets. Want that when item selected in list, to then go to that hyperlinked sheet.

On the sheet with list of hyperlinks (referred to by the properties) hyperlinks work ok.

How do I get the hyperlinks to stick when they are placed in the listbox ?

Thanks

Charles Harris

[Yongle]

Why bother with hyperlinks? Do not need them here.
Make sure that the all cells in Named List display the sheet name - this is the default when you set up the hyperlink to each sheet
This one line of code will take you to selected sheet. ( change "Listbox1" to the name of your listbox)

Private Sub CommandButton1_Click()


Sheets(ListBox1.Value).Select

End Sub

[Yongle]

Using hyperlinks within a listbox is a little harder and this is one way to do it

You will need to use a 2 column range for this solution, one column with the hyperlinks and the next column with the cell reference containing each hyperlink. (so cell B1 includes text A1 because that is where the link to sheet1 is)

COLUMN A Sheet1	COL B A1
Sheet2	A2
Sheet3	A3

When you set up the listbox use the 2 column as the RowSource
The code is not too difficult. I used the name "Listbox_CellRef " - change that to your listbox name

Private Sub CommandButton1_Click()


SheetAddress = ListBox_CellRef.List(ListBox_CellRef.ListIndex, 1)
Range(SheetAddress).Hyperlinks(1).Follow
Unload Me


End Sub