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..

 

[mikerickson]

That could be done, but I would consider a ComboBox or ListBox that would show all of the sheet names.

BTW, in the normal view window, there are little arrows for navigating the strip of Tabs (in Excel 2013 they are to the left of the sheet tabs). If you right click on those arrows, a pop-up listing all the sheets appears to help you select your sheet.

 

[AkaTrouble]

i had similar problem and solved it by creating a INDEX page that updates on opening workbook

create a page called INDEX and place the following code in the sheet module

Option Explicit
Sub Worksheet_Activate()
    Dim sh As Worksheet
    Dim cell As Range
    Dim I As Long
    Columns(1).ClearContents
    Cells(1, 1) = "Sheets Index"
    I = 2
    For Each sh In ActiveWorkbook.Worksheets
        If ActiveSheet.Name <> sh.Name Then
            Cells(I, 1).Hyperlinks.Add Anchor:=Cells(I, 1), Address:="", SubAddress:= _
                                       "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
            I = I + 1
        End If
    Next sh
End Sub

 

[SimmonsDeux]

That could be done, but I would consider a ComboBox or ListBox that would show all of the sheet names.

BTW, in the normal view window, there are little arrows for navigating the strip of Tabs (in Excel 2013 they are to the left of the sheet tabs). If you right click on those arrows, a pop-up listing all the sheets appears to help you select your sheet.

Thanks for the response!
The reason I am asking is that I have well over 300 worksheets.... I know. Don't ask.. In my userform, I have a listbox with the sheet names, I just want another avenue to locate and activate a specific sheet.
Thanks again!

 

[SimmonsDeux]

i had similar problem and solved it by creating a INDEX page that updates on opening workbook

create a page called INDEX and place the following code in the sheet module

Option Explicit
Sub Worksheet_Activate()
    Dim sh As Worksheet
    Dim cell As Range
    Dim I As Long
    Columns(1).ClearContents
    Cells(1, 1) = "Sheets Index"
    I = 2
    For Each sh In ActiveWorkbook.Worksheets
        If ActiveSheet.Name <> sh.Name Then
            Cells(I, 1).Hyperlinks.Add Anchor:=Cells(I, 1), Address:="", SubAddress:= _
                                       "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
            I = I + 1
        End If
    Next sh
End Sub

Is there any way to modify this to use in my userform?

 

[mikerickson]

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

 

[AkaTrouble]

Is there any way to modify this to use in my userform?

see above

.

mikerickson copied thanks

 

[SimmonsDeux]

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

You truly are the one and only highlander.... OF EXCEL!
Thanks a ton! And I'll have more questions in the future!

 

[SimmonsDeux]

Can i piggyback post??
If not, please move to new thread...

Under my text box on my userform, I have two buttons (Add and Delete). How do I make them function with my textbox in the following ways:

Add Button: Adds a new worksheet with the textbox value as the name. This worksheet will be a copy of a template worksheet (sheet 2) and will place it alphabetically in line with the rest of the worksheets.

Delete Button: Deletes the worksheet whos name is in the text box. A message box pops up and asks for validation on the decision.

Thank you!!

 

[mikerickson]

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.