Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for a Specific Worksheet Macro or Function?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using search function in macro | Excel Discussion (Misc queries) | |||
Excel Macro Query - Search for specific text in cells of a column | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Macro to search a column for a specific word and replace with | Excel Programming |