OK, a little complicated to explain, but here goes:
First, I have created a userform called "usfOne". On it are a listbox called "lstSheets" and a command button called "cmdGo".
Next, I have created an ordinary module, and put the following code in it:
Sub AddSheetsToList()
usfOne.lstSheets.Clear
For Each sheet In ActiveWorkbook.Sheets
usfOne.lstSheets.AddItem (sheet.Name)
Next
usfOne.Show
End Sub
Finally, you need this code in the click event for the "cmdGo" button:
Private Sub cmdGo_Click()
Sheets(usfOne.lstSheets.Value).Activate
usfOne.Hide
End Sub
When you run the macro "AddSheetsToList" it will put all the sheets in the active workbook into the list box. When you select one of them and click the "cmdGo" button, it will activate the sheet you have selected.
You could do away with the button and make it a double click on the sheet you want, or numerous other options. Note: this will probably fall over if you have hidden sheets - you will need additional code to unhide them first.
Hope this helps!
Phil.