none
VBA - Filter ListBox For Worksheet Names - Then Show Worksheet When Selected RRS feed

  • Question

  • Hello Friends,


    I am trying to Filter a listbox.

    The Listbox has a list of all my worksheet names.

    These worksheets are normally hidden.

    I created a textbox  - in this text box I would type the name of sheet and it would show all sheets with that keyword.

    when I select the sheet it will unhide it


    I am at stuck cross roads :(

    Private Sub TextBox1_Change()
        Dim i               As Long
        Dim n               As Long
        Dim Str             As String
        Str = Me.TextBox1.Text
        n = Me.WorkSheetBox.ListCount
        For i = 0 To n - 1
            If Left(Me.WorkSheetBox.List(i), Len(Str)) = Str Then
                
                
                Me.WorkSheetBox.ListIndex = i
                
                
                Exit Sub
            End If
        Next i
         
    End Sub


    http://jmp.sh/v/rXY1NLaY03DkLFFqo1ia

    please can anyone take a look at this and see what i need to do

    Cheers Dan

    Friday, July 8, 2016 7:15 PM

Answers

  • Private Sub TextBox1_Change()
        Dim iWorksheet As Worksheet
        
        WorkSheetBox.Clear
        For Each iWorksheet In ThisWorkbook.Worksheets
            If LCase(iWorksheet.Name) Like "*" & LCase(TextBox1.Text) & "*" Then
                WorkSheetBox.AddItem iWorksheet.Name
            End If
        Next iWorksheet
    End Sub


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by Dan_CS Saturday, July 9, 2016 11:45 AM
    Saturday, July 9, 2016 4:09 AM

All replies

  • Private Sub TextBox1_Change()
        Dim iWorksheet As Worksheet
        
        WorkSheetBox.Clear
        For Each iWorksheet In ThisWorkbook.Worksheets
            If LCase(iWorksheet.Name) Like "*" & LCase(TextBox1.Text) & "*" Then
                WorkSheetBox.AddItem iWorksheet.Name
            End If
        Next iWorksheet
    End Sub


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by Dan_CS Saturday, July 9, 2016 11:45 AM
    Saturday, July 9, 2016 4:09 AM
  • Thank you Felipe,

    I knew all the examples I saw were too complicated - for my simple task

      If LCase(iWorksheet.Name) Like "*" & LCase(TextBox1.Text) & "*" Then

    This is the magic line

    thank you so much it worked :)

    Good weekend to you


    Cheers Dan

    Saturday, July 9, 2016 11:44 AM