Make autofilter react based upon selection in drop box...

I have a pricing model I'm working on where the request is to have all of the line/part items in the model, but only display a subset of those items based upon a sub-line chosen by the user by a drop box that includes all sub-lines.

Right now the list of parts is a table that I'm querying that also includes a simple '1' or '0' to indicate whether every part is an element of one or many sub-lines; eg (sorry, sure this won't format right):

Item SubA SubB SubC
210-1010 1 1 0
210-1011 1 0 1

So if choosing SubB in the drop box, I need the autofilter to show the first part, but not the second one. I think I have to do it this way (autofilter) because it will retain all of the parts whether the user is looking at them at this moment or not; they will use this tool to perhaps first price all SubB items, *then* go select SubC and price all of *those* items - and it needs to retain everything they've done, rather than a dynamic query that would lose prior changes and pull in a whole new set of data (if that makes sense).

So.... I need my autofilter to react not to specifically choosing data on the autofilter itself, but to a choice made in the drop box (probably a macro/VB attached to the box that reacts to the linked cell value that results from the choice, but I don't know how to tell it if the linked cell value is '5', go to column 7 (first two are part number and description) in the autofilter and filter on that column.

Thanks!!

An example workbook would help

Which column contains bioresorb and which has the 1?

I would use a ComboBox from the Controls tools.

Set the ListFillRange to the list on sheet2, tip: create a Named Range & use that.

Then add this code to the ComboBox

Please Login or Register  to view this content.

It dosn't need a class module. The code should be in the sheet module, the Me refers to the sheet.

If you right click on the combobox in Design View & select view code you will open the VB Editor, paste the code there.

Here's an example

I thought it was working when a different selection is made. Does this work/

Please Login or Register  to view this content.

A pleasure, can you rate the reply?