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

--
sns
-----------------------------------------------------------------------
snsd's Profile: http://www.excelforum.com/member.php...fo&userid=1591
View this thread: http://www.excelforum.com/showthread.php?threadid=27456

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


---
Regards,
Norman



"snsd" wrote in message
...

Hi:

I have a Workbook with about 200 individual worksheets. To find a
specific one, I right click on the bottom lower corner of the screen
where the "browse worksheets" buttons are, select "more sheets" and
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 to
make this process a little less combersome? (eg. a button that would
bring up a box similar to the one I eventually get to in the above
proces?)

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

ComboBox1.Clear() 'Clear combo box

I'm a newbie and don't know how to fix it. Any ideas would be great if
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 4
functions. See the comments.
Sub populateCombo() 'populates the combobox
Dim tempWorksheet As Worksheet
Dim x, totalSheets
x = 1
COMBOBOX1.CLEAR() 'CLEAR COMBO BOX
totalSheets = ThisWorkbook.Sheets.Count 'counts the sheets in the
workbook
Do While x <= totalSheets
Set tempWorksheet = Sheets(x) 'The tempWorksheets has now the content
of the x worksheet
ComboBox1.AddItem (test.Name)'Put the worksheets name in the combobox

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 Sub


--
snsd
------------------------------------------------------------------------
snsd's Profile:
http://www.excelforum.com/member.php...o&userid=15910
View this thread: http://www.excelforum.com/showthread...hreadid=274564