Search for a Specific Worksheet Macro or Function?

[snsd]

Hi:

I have a Workbook with about 200 individual worksheets. To find
specific one, I right click on the bottom lower corner of the scree
where the "browse worksheets" buttons are, select "more sheets" an
type the first few letters of the worksheet name.

I have a question:

Does anyone know of a good macro or function... that I could add t
make this process a little less combersome? (eg. a button that woul
bring up a box similar to the one I eventually get to in the abov
proces?)

If not, I found the below macro that returns an error. The error is i
the 12th line. I've bolded it. I get the error "compile error: Synta
error.

ComboBox1.Clear() 'Clear combo box

I'm a newbie and don't know how to fix it. Any ideas would be great i
you don't have a better/simpler alternative.

Thx,

Dave

'makes the "searchform" appear when you start
Sub findWorkSheet() 'call this function!!
UserForm1.Show
End Sub

'To make things go our way, we need to write some code. Here is
functions. See the comments.
Sub populateCombo() 'populates the combobox
Dim tempWorksheet As Worksheet
Dim x, totalSheets
x = 1
COMBOBOX1.CLEAR() 'CLEAR COMBO BO
totalSheets = ThisWorkbook.Sheets.Count 'counts the sheets in th
workbook
Do While x <= totalSheets
Set tempWorksheet = Sheets(x) 'The tempWorksheets has now the conten
of the x worksheet
ComboBox1.AddItem (test.Name)'Put the worksheets name in the combobo

x = x + 1 'next worksheet!
Loop

End Sub

Sub DoesSheetExist()' Get the worksheet you are looking for to appear
Dim wSheet As Worksheet

On Error Resume Next
Set wSheet = Sheets(ComboBox1.Text)
If wSheet Is Nothing Then 'Does not exist
Set wSheet = Nothing
On Error GoTo 0
Else 'Exist
wSheet.Activate ' open the right worksheet
Set wSheet = Nothing
On Error GoTo 0
End If
End Sub

Private Sub ComboBox1_Change()
DoesSheetExist 'call function
End Sub

Private Sub UserForm_Activate()
populateCombo' when starting the combobox is filled
ComboBox1.SetFocus
End Su

 

[Norman Jones]

Hi SNSD,

Try:

Sub ShowSheetList()
'Based on code posted by Jim Rech
On Error Resume Next
If ActiveWorkbook.sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
On Error GoTo 0

End Sub