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