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
Excel worksheet search/find function
By Per A. Bergersen, published on 28 Jun 2002
| Filed in
You might also like...
VBA forum discussion
-
CorelDRAW VBA: cdrTraceLineDrawing FAILS, producing single linear path instead of Centerline trace?
by dancemanj (0 replies)
-
How to refresh ButtonPage in VBA for Internet Explorer
by inuyasha12 (0 replies)
-
Invitation to take part in an academic research study
by researchlab (0 replies)
-
Recreating MASTERMIND game in VBA. Sequence checking not working.
by horstbanga (0 replies)
-
Problem with printer...new computuer
by jhonbrighton11 (12 replies)
VBA jobs
-
VBA / Access / Excel Developer – Central London
Fruitful Business Services in London (EC1V), United Kingdom
£23-45k (DOE)
Comments