Search listbox from textbox

[zplugger]

Here is a example of what I would like to do, maybe someone can tell me if possible? If I need to read more data on this tell me Please, Have a large list and do not what to go on if not possible.

Thank You Z

[taps]

Hi Z,

Sorry, not able to got your problem. Could you please explain again?

[zplugger]

Hello taps
did you look at my workbook example? Not sure if you can filter a listbox. Maybe there is a work around for this. The command button will fill the listbox I hope, I will put the movies into genres to keep track of. only have two movies in the example,just not sure if all this is possible.

Z

[tigeravatar]

Z,

Attached is a modified version of your posted workbook.

I used column AA in sheet 'Data' to create a dynamic named range list_Movies which is defined with this formula:

=Data!$AA$2:INDEX(Data!$AA:$AA,MAX(2,ROWS(Data!$AA:$AA)-COUNTBLANK(Data!$AA:$AA)))

Then in the code module for sheet 'Main' is this bit of code for the textbox_change event:

Private Sub TextBox1_Change()
    
    Dim arrList As Variant
    
    With Me.ListBox2
        arrList = Application.Transpose([list_Movies].Value)
        .ListFillRange = vbNullString
        .List = Filter(arrList, Me.TextBox1.Text, True, vbTextCompare)
    End With
    
    Erase arrList
    
End Sub

Basically, when you use a button on the left-hand side, make sure the button code clears the textbox and then puts the correct movie list into column AA of sheet 'Data' starting at row 2 and then set the ListFillRange of the listbox to be list_Movies. Now when you type in the textbox, the listbox will only show movies with matching text in their title. Deleting text from the listbox will show matches and when you delete the last character so the textbox is empty the whole movie list will be displayed again.

[zplugger]

Thanks for the Great work, just a couple questions. Do you think it possible to push a button on the left and have it fill the listbox from the column on the data page. Can it open so all movies show in list box,if I go to action it only shows action movies if I have them in my list?.When I push all movies it will show all titles?. Again great job on the Search Part.

Z

[tigeravatar]

Z,

That is all possible, and I outlined how to do so in my previous post:

Basically, when you use a button on the left-hand side, make sure the button code clears the textbox and then puts the correct movie list into column AA of sheet 'Data' starting at row 2 and then set the ListFillRange of the listbox to be list_Movies. Now when you type in the textbox, the listbox will only show movies with matching text in their title. Deleting text from the listbox will show matches and when you delete the last character so the textbox is empty the whole movie list will be displayed again.

As for showing all movies when it opens, you can use the Workbook_Open event and use basically the same code that would be used for the All Movies button.

Give it a try and if you get stuck, post back.

[zplugger]

Thanks again tigeravatar, still amazed at your search job. I'm not real good at this but will work on it a couple days and try to get the buttons right. I like trying
different stuff but just slow. I will post back in a couple days on my results.
Z

[zplugger]

Made progress but ran into a snag, movies from the buttons on the left do not post right data? Only did action_adventure,comedy so far,here
is the new file on where I'm at.
Z

[tigeravatar]

Z,

Attached is a modified version of the workbook.

First, I started renaming buttons (only renamed the first 5) so I wouldn't have to doublecheck if CommandButton1 is All Movies or not.

• The All Movies button is now named btn_AllMovies
• The Action,Adventure button is now named btn_ActionAdventure
• The Children button is now named btn_Children
• The Comedy button is now named btn_Comedy
• The Drama button is now named btn_Drama

Then I created a function at the bottom of the 'Main' sheet code module. This function accepts a string argument that will match a header in the 'Data' sheet cells K1:Z1. It uses that match to populate the list_Movies named range (column AA starting at row 2):

Private Function PopulateMovieList(ByVal strCategory As String)
    
    Dim rng As Range
    [list_Movies].ClearContents
    With Me.ListBox2
        .ListFillRange = vbNullString
        .Clear
    End With
    
    With Sheets("Data")
        Set rng = .Range("K1:Z1").Find(strCategory, , xlValues, xlWhole)
        If rng.End(xlDown).Row = .Rows.Count Then Exit Function 'No movies in list
        Set rng = .Range(rng.Offset(1), rng.End(xlDown))
        rng.Copy Sheets("Data").Range("AA2")
    End With
    
    Me.ListBox2.ListFillRange = "Data!" &[list_Movies].Address
    
    Set rng = Nothing
    
End Function

Lastly, when the buttons are clicked they call that function and feed the appropriate string argument (again I only did 5, but you get the idea). Make sure that the string is an exact match to the appropriate header in the 'Data' sheet cells K1:Z1.

Private Sub btn_ActionAdventure_Click()
    
    PopulateMovieList "Action & Adventure"
    
End Sub

Private Sub btn_AllMovies_Click()
    
    PopulateMovieList "All Movies"
    
End Sub

Private Sub btn_Children_Click()
    
    PopulateMovieList "Children"
    
End Sub

Private Sub btn_Comedy_Click()
    
    PopulateMovieList "Comedy"
    
End Sub

Private Sub btn_Drama_Click()
    
    PopulateMovieList "Drama"
    
End Sub

[zplugger]

Thanks for you time on this tigeravatar, not sure what is wrong. If I go to all movies it shows all movies and when I click on one it changes perfect. If I go to action,adventure the movie 10 things I hate about you comes up but go to 10 items or less when I click the listbox? I see where it makes different changes in AA with the buttons.

Z

[tigeravatar]

Z,

The sheet acts like it is protected. You have a listbox_click event that changes the value in B8 to be the chosen listindex. That works great when movies line up with what the list index is, but that appears to be based on the list in sheet 'Data'. You need to use some sort of lookup instead of depending on the listindex. I can't actually see or seem to work with whatever is causing the image to change (i can't find code for it, and the only cell I can select on the Main sheet is A1) so i can't really give you advice on that. Basically if you change the order in the Data sheet (and I mean the whole row because the number in column A is never used), you'll get different results depending on the item chosen in the listbox.

---------- Post added at 01:36 PM ---------- Previous post was at 01:34 PM ----------

Although playing around with the sheet did make me realize there are a few bugs in the textbox_change event code. Here's updated code to correct those:

Private Sub TextBox1_Change()
    
    Dim arrList As Variant
    
    With Me.ListBox2
        arrList = Application.Transpose([list_Movies].Value)
        .ListFillRange = vbNullString
        .Clear
        If IsArray(arrList) Then
            .List = Filter(arrList, Me.TextBox1.Text, True, vbTextCompare)
        Else
            If InStr(1, arrList, Me.TextBox1.Text, vbTextCompare) Then
                .AddItem arrList
            End If
        End If
    End With
    
    If IsArray(arrList) Then Erase arrList
    
End Sub

[zplugger]

Thanks, I had help on getting the picture to show up. One problem I had in the begining was when I sort the list it messed everything up. I'm not real good at excel but I peg away at it with a lot of great help from people like you that take the time to help others. Not sure if this is the problem but it seems like I did a column row lock in the first row of data sheet if that helps.

Z

[zplugger]

I see what you mean tigeravatar , seems like the workbook is protected some how. I not even sure something like this can be done in Excel, I
just not that good to tell. I will rebuild the book again and start over, will keep it simple to start.I have all the drafts when I started and it shows the data going into B8 to make selection. Thanks again for all your time and I hope you look at new workbook when its done. It will take me a couple days with all the other things I have to do in life. I do think the book is damage some how and no one could fix it.

Z