Detect when autofilter drop-down selection made

Is it possible to detect when a auto-filter selection has been made ?
Is that when an autofilter hides rows, the activecell could become hidden, which potentially may cause errant data entry, not knowing what cell is receiving entry as it is hidden from view.

if the worksheet contains a change event that autosizes the target row, it unhides the filtered hidden row in the process.

I could say if target is hidden ignore autosize, but i would prefer to activate a visible cell (first visible cell for filtered column) on selection of autofilter.

Any suggestions ?

Hello wotadude,

If there are any internal Excel events associated with the auto-filter, they aren't exposed to VBA. You would need to create a workaround, but that depends on what you are trying to do. You would need to explain in detail what you want to do before it can be declared possible or not.

I certainly agree with Leith that more info would be good but I can't resist these as I always find them interesting...

One possible workaround would be to ensure you

a) have say 1 volatile calculation on your sheet containing the Auto Filter, eg

=NOW()

the above will ensure the Calculate event is triggered (given alteration of Row Visibility is Volatile Action) ...

b) apply a Calculate Event to the sheet object based on active row visibility

Please Login or Register  to view this content.

in the above I set this up to select the first visible cell below the headers - however if you simply want to select the header cell remove the Offset(1) from the above.

To insert the above right click on tab containing the filter - select View Code and past above into resulting window ensuring macros enabled thereafter.

I'm sure it's not watertight but I hope it gives you some ideas...