How to Identify the selected text from the dropdown menu of an autofilter using Maros

[bmanicse]

I want to identify the text of the selected text from the dropdown menu of an autofilter to utilise it in macros for calculation. How can I identify the text in macros.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If you have thing it is not a good question pardon my ignorance. I would be happy if somebody has some answer.<o></o>

 

[Colin Legg]

Hi,

It's doable but not easy.

You'll have to loop through the Autofilter's Filter collection and use some carefully built logic to examine the Filter.Criteria1 and Filter.Criteria2 arguments. It has to be done carefully to avoid raising errors - for example, you'll need to check the Filter.On and Filter.Operator properties to know what can be accessed. In Excel 2007+ it's even tougher because you have other issues such as sorting and colour criteria to contend with.

If you're struggling I do have an old project where I wrote some classes to store the autofilter settings. If you let me know which version of Excel you're using I could probably trim it down to something digestable and close to what you need.

 

[AlphaFrog]

To Colin:

I'm by no means an expert (though I play one on You Tube), but doesn't Filter.Criteria1 just return the selected item in the list with an equal sign in front of it? Why would you have to loop through the Filter collection?

Please pardon my ignorance.

 

[Colin Legg]

Hi AlphaFrog,

I'm assuming that multiple filters could be filtering. To get the criteria for each filter you'd loop through the filters collection. That's all I meant - nothing outrageous.

 

[AlphaFrog]

I get you now. Thanks.ray:

 

[bmanicse]

Hi Colin,

Thanks for the offer. I am uisng office XP pro. I am looking for a code that fires when ever we choose a text from the dropdown list in the autofilter. I tried the worksheet change event, it doesn´t work.

Thanks

 

[mikerickson]

To simulate an OnFilter event one could use the Calculate event. Put =COUNTA(A:A) in a spare cell. Then hiding any row, either manualy or via AutoFilter, will trigger a re-calculation of that formula and the Calculate event will fire.
Oddly enough:
Autofilter on one term
Autofilter on the same term

will trigger it twice, its not an AutoFilter_Change event its an ApplyFilter event. (Or HideRows).