how can I create a macro which will check the selected criteria in filter?

I want to create a macro which will check selected criteria in filter, how would i do that?

can any one provide me an example?

Thanks,
Anand

Thanks Jim, this code works partially.

It does not work in two scenario,

Scenario 1: When we select all, function does not show any output

Scenario 2: when first criteria of filter is not selected and if we select 2nd and 3rd criteria, then function does not show any output.

Any suggestion?

Scenerio 1 : Yes, it is correct that the cell will show nothing. There is no filter to show. If you would like to show something you could replace FilterCriteria = "" with FilterCriteria = "Table Not Filtered"

Scenerio 2 : I'm not sure. I use the dropdown buttons to select the filter one at a time. I suppose you are applying a filter to several criteria at one time. I have to look into that.. give me a few minutes

let me try this and I will post if it works or not, Thanks!

It looks like I am missing something here, in above spreadsheet, I have unlocked it and wrote a function "=FilterCriteria(5,5)" in cell Q5, and it does not show any value of filter criteria even if I have one criteria selected. any suggestion?

Still it does not show anything, I only have one filter criteria selected which is one animal name.

Is your sheet set to auto-calculate? Did you comment out the line "Application.Calculation = xlManual in Sub Workbook_Open() ? When you put "=FilterCriteria(8,5)" into cell Q5, was that cell set to General (otherwise it will think the code is text)? Cant think of anything else.

Try..
Clear the filter on the Table
Right click on any cell to activate the filter
Repeat right clicks on any other fields
This should show the filter criteria in the cell where you placed FilterCriteria().
Also you can use the built in Excel buttons on the header row of the table to change the filter

check the attached spreadsheet, it still does not show any thing in cell "Q5".

Thank you, I think I will spend some time on this and let you know by tomorrow if I have any more questions, Thanks!

You're welcome Anand. I'll watch the post. Good luck

Here is the file that includes worksheet calculation to Automatic still cell "B2" does not update if I choose some filter criteria in column name "Animal name".

Any suggestion on what am I missing?

Thanks!

Hi Anand. The file you posted is fine, should work. The only thing I can think of is that you dont have macros enabled. Make sure you enable macros so the vba code can run

well the problem is cell "B2" shows value when I right click on any cell inside table but instead if I choose filter criteria manually then it does not update the cell "B2". I checked and Macro is enabled .

The last file you posted is working fine. The only way that B2 would not update would be if you placed the calculation mode back into manual. Look in the "Formulas" tab, make sure that "Calculation Options" is set to "Automatic". Should work.

I did check it and "Calculation Options" is set to "Automatic". any other ideas?

The last file you re-posted works fine. Maybe you unproected the file, and then re-protected it without selecting "Sort" and "Use Autofilter"? The "Sort" and "Use Autofilter" must be checked to use the built-in table filter controls. Other than having "Calculation Options" set to "Automatic" there really cant be anything wrong. Check again the file you last posted and try using that file, maybe by accident you made some change to the vba code and thats why it isnt working now.

Jim, you are right, I looked into that file again and it works fine, so it looks like I did something to break it.

Thank you!

Glad to help Anand. If you found my solution helpful to you, kindly click the star on the helpful posts to give me rep points. Also mark this thread as solved. Good luck