AutoFilter

[jstevens]

Is it possible to capture by code whether or not an AutoFilter selection was changed.

As an example say there were three columns each having an AutoFilter. It doesn't matter which column was changed just that fact that one changed is what I'm after.

Thanks,
JOhn

[HansV]

Excel doesn't have a specific event that signals a change in the filter.
If you have at least one formula that refers to cells in the filtered range, the Worksheet_Calculate event will occur when the filter is changed. You could use this to inspect the AutoFilter object, but it'd be a lot of work.
Do you really need this, and if so, why?

[jstevens]

Hans,

I'll work with the Worksheet_Calcuate event so that when an AutoFilter object is selected it will write a formula referencing the filtered range. I know how to do that. It's just when the AutoFilter is reset to it's normal state (nothing selected) that I would want to clear the formula.

Regards,
John

[HansV]

ActiveSheet.AutoFilter.FilterMode is True if a filter is applied (i.e. some rows are hidden), False if no filter is applied (i.e. all rows are shown).

[jstevens]

Thank you,
John

[jstevens]

Hans,

How would one incorporate a line of code within a "Private Workbook_SheetCalculate(ByVal Sh as Object)" to write a formula to the ActiveSheet containing the AutoFilter? I have not had much luck.

I had a previous post with my code. The only thing different is that it's now going to a Private Sub.

One more thing...when the AutoFilter is set back to it's normal state (nothing is being filtered) then the formula should be deleted.

Regards,
John

[HansV]

You could use something like this:

sh.Range("B1").Formula = "=TODAY()"

Important: you must set Application.EnableEvents to False before this line (and back to True after it). Otherwise, you may end up with an endless loop, causing Excel to crash.