create drop down box to select multiple criteria and link macro to each criteria

I need idea on how to create a drop down box with multiple criteria selection which should also include "Select All" and "Select None".

and finally I want to link a macro to each selection criteria.

Any suggestion on how would I start or sample file that would show this feature?

Thanks!

Anand

Anand

You can't select multiple items in a dropdown.

So Is there a way I can attach a macro to each filter criteria in filter drop down box?

You can't attach a macro to filter criteria.

What is it you are actually trying to do?

well, I am generating a report for 7 products, currently macro works fine and I have macro for each product. Now I want to add one more functionality so that user can select for which product they want to generate a report, they can choose multiple product or all product or just one product.

I was thinking of using check boxes and putting all of them in one Group box and i think it might work fine. Instead of this I was thinking if I can create one cell with a drop down box which shows product names and user can select multiple product or all product from here and based on selection once they click on generate report button user will see appropriate product reports in different worksheets.

You should go with the checkboxes and add a button..

The button would check each checkbox and if it's checked it will run the code associated with the product that checkbox is for.

By the way, is the code for each product similar?

yes, it is same.

If the code for each product is the same you probably don't need separate subs.

You can use a sub that takes the product as parameter.

yes, I am using it same way, using product name as parameter only, I think while explaining my problem I missed that part or did not mention it clearly.

Thank you!

I can also create a filter and based on filter criteria selection when we click on button to generate report it will only generate report for criteria selected in filter, correct?

Is it even possible?

Not sure what you mean.

You don't really 'create' a filter, you apply a filter to data and then you can select criteria to filter the data.

well what I was thinking was, to create filter in one column and hide the data which is product name in my case either by hiding the rows or selecting the font color as white so that no one can see it. So this way I will be able to create a drop down box and can select multiple criteria in it.
As a next step I will create a button and that button will check which filter criteria is selected and based on that will generate report for each filter criteria.

I know this may not be the cleanest approach but this is the best i could come up with, would really appreciate any suggestion to make it better.

Hope this clears the confusion.

Thanks!

Sorry it actually confuses more.

You can't select multiple criteria from a dropown, as I originally said.

Perhaps you could attach a sample workbook which shows what you have and what you are trying to achieve?

well I will use filter as a drop down box to select multiple criteria.

Now my question boils down to one thing, that is, how can I create a macro which will check the selected criteria in filter?

Why do you need to check the selected criteria?

Won't the data be filtered as required already?

Criteria in filter box would be my product name and I want to run report only for selected products.

So the only reason for filtering would be to get the products for the report?

That doesn't seem to make sense, and having a filter might interfere with creating the report.

I say might, because I've no idea how you are doing that.

I'm sure a sampe workbook would help clear things up though.

my data is not in the same workbook, i am getting data from 12 different workbooks. so it will not at all affect creating report.

So the filtered data has nothing to do with the report?

that is correct, from filter I want to extract the selected criteria, that's all.

Again to summarize my question, I want to know how can I create a macro which will check the selected criteria in filter?