Advanced filter refresh after criteria change

[NesYo]

Hi, I am trying to figure out how to automatically refresh an advance filter (no manual running of macro), when some of my filter criteria changes, provided the change is indirect - first changes another field (named "interface"), that then triggers the change in one of the criteria which calculates to either "True' or "False".

My final goal is when the file user changes field "Interfaces" (which is a of list type with 3 possible values), which produces a change in the criteria for the advanced filter, the latter to refresh itself automatically. Tried a few things but neither worked. Any ideas?

 

[mikerickson]

Instead of a validation drop down, use a drop down from the Forms menu which will trigger an AdvancedFilter macro.

 

[NesYo]

Instead of a validation drop down, use a drop down from the Forms menu which will trigger an AdvancedFilter macro.

Thanks Mike, nut didn't quite get it. I made a Form Control Combo Box if you meant that, but the advanced filter still needs manual refresh to apply. I made a macro to do it but how to automatically run it?

 

[mikerickson]

If you right click on the Combo Box, Assign Macro is on of the options in the popup menu. Assign your macro to the control and it will be run everytime that the user changes the value of the ComboBox.

 

[NesYo]

If you right click on the Combo Box, Assign Macro is on of the options in the popup menu. Assign your macro to the control and it will be run everytime that the user changes the value of the ComboBox.

Thanks again Mike. Tried it but ended up with an error:

Run-time error '1004'
This can't be applied to the selected range. Select a single range in a cell and try again.

The highligthed area of the macro debugger is:

Range("A10:C84").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A4:D7"), Unique:=False

 

[mikerickson]

Are all the criteria for Advanced filter met.
Blank rows and columns on all sides of the data set
Unique (non-blank) headers on each column
No blank rows in the data set?

Does your data extend below row 84?

A4:D7 seems like a very big criteria range. If you are filtering only one field "Interfaces" it should have only one column, and probably only one row (depending on how many different interfaces you are wanting to see)

 

[NesYo]

Are all the criteria for Advanced filter met.
Blank rows and columns on all sides of the data set
Unique (non-blank) headers on each column
No blank rows in the data set?

Does your data extend below row 84?

A4:D7 seems like a very big criteria range. If you are filtering only one field "Interfaces" it should have only one column, and probably only one row (depending on how many different interfaces you are wanting to see)

Well, all criteria met I'm afraid. My data is 3 columns and I am filtering it on one of the columns (Interface), plus one more that is evaluated to true or false by changing the form control value (list range) which is on another page. Can send a simple file for review but the site does not allow attachments.

 

[mikerickson]

If you are filtering it on one column with an OR, then the criteria range should be one column.

Have you tried manualy AdvancedFiltering it with the critieria range, etc as they are when the code fails?