Search listbox from textbox

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Z,

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

Originally Posted by tigeravatar

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.

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):

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.