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

You might also like...

Comments

Per A. Bergersen

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.” - Rich Cook