Userform Textbox to Search and open worksheet in excel

[SimmonsDeux]

All,
I have a workbook with an absurd number of worksheets... Is there any way I can use a text box in a userform to search and open a worksheet??

I already have a userform with a textbox and command button... I'd like it so that after you enter text into the box and click the button, one of two things happen:

1. It locates and opens the appropriate worksheet.
2. If no worksheet is found, a message box pops up and says "No Record Found"

Any help would be greatly appreciated..

 

[SimmonsDeux]

This is code for a user form with a text box, a listbox and 4 command buttons, butAdd, butClose, butDelete and butGo.
Your post sounds like your worksheets are alphabetized and that you have a template for the added sheets.
This code assumes that that template sheet is named "Template"
It also assumes that there is a Master sheet that you want to be to the left of all sheets and is separate from the alphabetization.
If there is more than one unsorted sheet at the left of the tabs, change the FirstFreeSheet variable in SortAllWorksheets as needed. (If all the sheets are in the sort, FirstFreeSheet should equal 1). You might want to include the template sheet in the unmoving sheets.

Dim HomeSheet As Worksheet

Private Sub butAdd_Click()
    If TextBox1.Text <> vbNullString Then
    With ThisWorkbook
        If MsgBox("Add a new sheet named " & TextBox1.Text & vbCr & "There is no undo.", vbYesNo + vbDefaultButton2) = vbYes Then
            .Sheets("Template").Copy after:=.Sheets(.Sheets.Count)
            ActiveSheet.Name = TextBox1.Text
            Set HomeSheet = ActiveSheet
            Call AlphabetizeAllWorksheets
        End If
    End With
    End If
End Sub

Private Sub butClose_Click()
    HomeSheet.Activate
    Unload Me
End Sub

Private Sub butDelete_Click()
    With ListBox1
        If .ListIndex <> -1 Then
            If MsgBox("Delete sheet " & .Text & vbCr & "There is no undo.", vbYesNo + vbDefaultButton2) = vbYes Then
                Application.DisplayAlerts = False
                ThisWorkbook.Sheets(.Text).Delete
                Application.DisplayAlerts = True
                UserForm_Initialize
            End If
        End If
    End With
End Sub

Private Sub butGo_Click()
    Unload Me
End Sub

Sub AlphabetizeAllWorksheets()
    Dim FirstSheet As Worksheet
    Dim FirstFreeSheet As Long
    Dim i As Long
    Dim j As Long
    
    FirstFreeSheet = 2
    With ThisWorkbook
    For i = .Sheets.Count To FirstFreeSheet Step -1
        For j = FirstFreeSheet To .Sheets.Count
            If LCase(.Sheets(i).Name) < LCase(.Sheets(j).Name) Then
                .Sheets(i).Move before:=.Sheets(j)
                Exit For
            End If
        Next j
    Next i
    End With
End Sub

Private Sub ListBox1_Click()
    ThisWorkbook.Sheets(ListBox1.Text).Activate
    butDelete.Enabled = True
    butGo.Enabled = True
End Sub

Private Sub TextBox1_Change()
    Dim i As Long
    With ListBox1
        .ListIndex = -1
        butDelete.Enabled = False
        butGo.Enabled = False
        If TextBox1.Text <> vbNullString Then
            For i = 0 To .ListCount - 1
                If LCase(.List(i)) Like LCase(TextBox1.Text & "*") Then
                    .ListIndex = i
                    butDelete.Enabled = True
                    butGo.Enabled = True
                    Exit Sub
                End If
            Next i
        End If
    End With
    butAdd.Enabled = Not (butDelete.Enabled) And (TextBox1.Text <> vbNullString)
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long, j As Long, oneSheet As Worksheet
    With ListBox1
        .Clear
        For Each oneSheet In ThisWorkbook.Sheets
            If 0 < .ListCount Then
                For j = 0 To .ListCount - 1
                    If oneSheet.Name < .List(j) Then
                        Exit For
                    End If
                Next j
            End If
            .AddItem oneSheet.Name, j
        Next oneSheet
    End With
    butGo.Enabled = False
    butAdd.Enabled = False
    butDelete.Enabled = False
    Set HomeSheet = ActiveSheet
End Sub

Note: there is no error checking for illegal sheet names of added sheets.

Daer God.... Your mind is so beautiful... Thank you. Works like a charm!!!

 

[SimmonsDeux]

Daer God.... Your mind is so beautiful... Thank you. Works like a charm!!!

One more for ya,
I've got two sheets (named INDEX and Template) That I don't want showing up in my list box on the userform... How do I go about this?

 

[mikerickson]

With the last code I posted.
Use Edit>Move to move those sheets to the far left of the the tabs.
Then in the Sub AlphabetizeAllWorksheets, change the variable FirstFreeSheet to 3.

Then add these two lines

Private Sub UserForm_Initialize()
    Dim i As Long, j As Long, oneSheet As Worksheet
    With ListBox1
        .Clear
        For Each oneSheet In ThisWorkbook.Sheets
           [COLOR="#FF0000"] If 3 <= oneSheet.Index Then[/COLOR]
                If 0 < .ListCount Then
                    For j = 0 To .ListCount - 1
                        If oneSheet.Name < .List(j) Then
                            Exit For
                        End If
                    Next j
                End If
                .AddItem oneSheet.Name, j
            [COLOR="#FF0000"]End If[/COLOR]
        Next oneSheet
    End With
    butGo.Enabled = False
    butAdd.Enabled = False
    butDelete.Enabled = False
    Set HomeSheet = ActiveSheet
End Sub

 

[Ayra88]

This is code for a user form with a TextBox, a ListBox and two command buttons (butCancel and butGo)

Dim HomeSheet As Worksheet

Private Sub butCancel_Click()
    HomeSheet.Activate
    Unload Me
End Sub

Private Sub butGo_Click()
    Unload Me
End Sub

Private Sub ListBox1_Click()
    ThisWorkbook.Sheets(ListBox1.Text).Activate
End Sub

Private Sub TextBox1_Change()
    Dim i As Long
    With ListBox1
        .ListIndex = -1
        If TextBox1.Text <> vbNullString Then
            For i = 0 To .ListCount - 1
                If LCase(.List(i)) Like LCase(TextBox1.Text & "*") Then
                    .ListIndex = i
                    Exit Sub
                End If
            Next i
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long, j As Long, oneSheet As Worksheet
    With ListBox1
   
        For Each oneSheet In ThisWorkbook.Sheets
            If 0 < .ListCount Then
                For j = 0 To .ListCount - 1
                    If oneSheet.Name < .List(j) Then
                        Exit For
                    End If
                Next j
            End If
            .AddItem oneSheet.Name, j
        Next oneSheet
    End With
    Set HomeSheet = ActiveSheet
End Sub

@mikerickson
Hi Mike, I know this thread is old, but I would appreciate it a lot if you could help me (I am a complete beginner). I have the same problem as the OP and I tried your code: I have a workbook with many worksheets, being named after the IDs of the users. The Userform would pop up as the user opens the excel and asks for their ID. Then they could either choose it in the list or enter it in the textbox, and their workbook should be open. Your code worked out brilliantly!

However, if the user enters an ID that is not availabe in the listbox (there is no sheet named after them), nothing happens. I would like to bring this to another level:

- when the user misspells their name in the textbox, or if there is no sheet naming after them, and press OK, I would like a msgbox to open and say "Wrong ID or ID not available, please try again"
=> User clicks on OK => Then the userform should appear again
- This should continue until the user enters their name correctly
- After 3 attempts the msgbox should show "It seems that your ID is not available. Please contact [...]" => OK on the Msgbox should close both Msgbox and Userform.

I hope this is not too complicated. Many thanks in advance for your help!

Best regards, A