Finding worksheet based on user input

[jrudolph]

I have a workbook with a bunch of individual worksheets, that will continue to grow overtime. Each spreadsheet is a statement for billing for individuals and their may be similar names.

I have a "Search" button on the main worksheet page. I want to be able to enter text into cell "B3" and then click search and be taken to the page closest to what I type in. The workbook is sorted alphabetically so if I am taken to the first closest match I can search from there.

For example maybe I have two Smith, J worksheets (the second of which may be listed as Smith, J (2)). I want to be able to type in, "Sm", "Smi", "Smith" or "Smith, J" and be taken to the first "Smith" worksheet in the workbook.

Further if no matches come up, I would like there to be an message box that says that.

So far I have the following basics. I am not really sure where to go from here.

Sub Search_Sheets()
 
    Dim SearchName As String
 
    SearchName = ActiveSheet.Range("B3").Value
 
    If SearchName <> "" Then
    Sheets(SearchName).Select
 
    Else
    MsgBox "Terminate", vbOKOnly, "Error"
    End If
 
End Sub

 

[tweedle]

You could record a .Find macro and utilize that, though it would be looking for cell values (Billing name/Invoice Number and the like), not sheet names.


There's another option to utilize a Combobox (activex)
This is for a ComboBox on Sheet1

Private Sub Worksheet_Activate()
'Every Time you activate Sheet1, the Combo Box re-loads
'Goes in Sheet1
        EnableEvents = False
        ComboBox1.Clear
        For shtIdx = 2 To Sheets.Count
            ActiveSheet.ComboBox1.AddItem Sheets(shtIdx).Name
        Next
        EnableEvents = True
End Sub
Private Sub ComboBox1_Click()
'Goes in Sheet1
'Activates the selected sheet
    Sheets(ComboBox1.Text).Activate
End Sub

 

[jrudolph]

Thats not what I was looking for... but that is a pretty sweet deal. Simple and does better than what I was actually looking for!

Thanks!

 

[jrudolph]

What if I want to include all sheets in the drop down, except one? How would that look?

The sheet I want to exclude is Sheet2 (BillingTemplate)

If its an easy tweak that would be great, otherwise its not a big deal. Thanks again!

 

[tweedle]

This is more along the lines of your orig. rqst.

Private Sub CommandButton1_Click()
   Dim SearchName As String
 
    SearchName = ActiveSheet.Range("B3").Value
    SheetFound = False
    
    For ShtIdx = 1 To Sheets.Count
        If Sheets(ShtIdx).Name Like "*" & SearchName & "*" Then
            SheetFound = True
            Sheets(ShtIdx).Activate
            Exit For
        End If
    Next
    If Not SheetFound Then MsgBox "Terminate", vbOKOnly, "Error"
End Sub

 

[tweedle]

For shtIdx = 3 To Sheets.Count