Need Combo Box to Filter Rows

Good morning!

I need help with VBA code to filter an Excel Table to show only rows that contain "x" in the chosen column of an ActiveX Combo Box?

For example (see attached), the table has one column for each month. An "x" appears in one or more of the months. When I choose "May" from the dropdown list I want to filter the table to show only rows where and "x" appears in the May column.

What complicates this is that many rows contain an "x" in multiple month columns.

Any suggestions would be most appreciated.

Thanks,
tom

Hi CK76,

Thanks for the help!

I wonder if the code could be modified so that the filtering happens instantly instead of having the user click away from the combo box.

Also, I would love to get your input on how this need could be achieved with data validation.

Thanks again.
tom

Oops...one more thing. What needs to be done to get back to the full list without filtering. That would be the starting point but should also be a selection in the combo box.

tom

mehmetcik, I am attaching the file. I probably did something wrong but you can see the results here.

Hi mehmetcik,

I really like your solution so I am hoping you can help me to get it working for me. I think it is picking the month earlier than chosen (for example, April when May was chosen). An then the part about the messed up interface after running it a time or two. One other thing. The _activate() for some reason does not populate the combo box when opening the file. I would have thought opening was akin to activate but perhaps not.

I so hope you can guide me.

Thanks again,
tom

Use the Workbook_Open event to load the combo box.

In Format Control for the combo box set Properties to Don't move or size with cells.
In properties from the right click menu set ListRows to 13.

Hey NoSparks...got a question.

In the code you used the month +1 to get to the right column. It works perfectly, of course. My ACTUAL workbook uses column headers that are not months but labels for various types of documents. How would we modify the code to choose the right column when we need to reference the row of combo box options instead of a calendar month?

Thanks,
tom

Make the list exactly what the headers are then this should do

Please Login or Register  to view this content.

Hi NoSparks,

Your solution is magnificent!!! It works perfectly, of course. Thanks ever so much for the help.

All the best,
tom