Library code snippets
Excel worksheet search/find function
Having problems with finding the right worksheet? This code makes it easier
find what you are looking for. The search criteria is the worksheet names that
is put into a combobox automatically. when clicking on the name or writing it,
the worksheet with that name appears.Add a userform, userform1. Put a combobox
on it. Write this in your module (Module1): '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
Related articles
Related discussion
-
How to control IE from VBA
by NaseemAhmed (0 replies)
-
Excel 2007 Error : ' the digital signature is deleted when Excel users tries to save the Master Excel Spreadsheet.'
by jdierkes (0 replies)
-
Excel VBA transposition problem
by KDHTSVAU (2 replies)
-
excell question
by Uncle (11 replies)
-
Question about subforms
by ginwah (0 replies)
hi everyone,
i put the code as it is in my vba, and created the 2 sheets (userform, userform1) but i Always end up having a problem with these 2 parts,
Set tempWorksheet = Sheets(x) 'The tempWorksheets has now the content of the x worksheet (says syntax error)
Set wSheet = Sheets(ComboBox1.Text) (says Sub or function not defined and marks Sheets in the line)
can anyone please help me ? would be really appreciated , thanks
hi everyone,
i put the code as it is in my vba, and created the 2 sheets (userform, userform1) but i Always end up having a problem with these 2 parts,
Set tempWorksheet = Sheets(x) 'The tempWorksheets has now the content of the x worksheet (says syntax error)Set wSheet = Sheets(ComboBox1.Text) (says Sub or function not defined and marks Sheets in the line)can anyone please help me ? would be really appreciated , thanks
ComboBox1.Clear()
to
ComboBox1.Clear
No arguments required...
VERY useful by the way - I'd been trying to think of a way to do this efficiently!
- Morgan
Set tempWorksheet = Sheets(x) 'The tempWorksheets has now the content of the x worksheet
ComboBox1.AddItem (tempWorksheet.Name) 'Put the worksheets name in the combobox
change test.Name to tempWorksheet.Name
Would anyone be so kind as to tell me how to select multiple worksheets from a workbook? Specifically, the workbook has about 100 worksheets, and I'd like to pull out around 50 specific ones. Can anyone help??? Thank you very much.
-yk
using System.Runtime.InteropServices;
excelObj.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelObj);
Marshal.ReleaseComObject(excelObj);
excelObj = null;
and then do a Garbage Collect. Thats what it took for me... Hope it helps.
Thanks for good topic.
Hi I Need to write a component which will close all instances of the excel files,those are opend from different sources.Can u help me on this.
[3]Write this in your module (Module1): [/3]
'makes the "searchform" appear when you start
Sub findWorkSheet() 'call this function!!
UserForm1.Show
End Sub
The rest of the code is "on the" userform.
Also there should be "tempworksheet.name" and NOT "test.name" in the function Sub populateCombo() !!!
Per A. Bergersen
This thread is for discussions of Excel worksheet search/find function.