User Form Check Boxes and Adv Filters

[gr6au]

Hi, could someone please help.

I have created a userform with 6 check boxes and labels which relate to the content of columns D and P in a table. The user can select any combination of these check boxes. There are also 2 command buttons; one to Enter and one to Cancel.

[x] London store (Column D)
[ ] Cambridge store (Column D)
[ ] Bananas (Column P)
[x] Oranges (Column P)
[x] Apples (Column P)
[ ] Pears (Column P)

What I would like to happen is for the check boxes to define the criteria for an advanced filter. For example, if the user were to select check boxes 1, 4 and 5, the table would be filtered to show only the rows that contain 'London store' and 'Oranges' OR 'London Store' and 'Apples'.

What I'm struggling with is the code to put behind the user form, check boxes and command buttons. Also, 6 checkbox choices = 64 possible combinations so I'm assuming I need to have a spearate criteria table showing all 64 permutations for the adv filter to look at?

Help on any of the above will be very gratefully received

 

[GlennUK]

As far as I can see there are only 8 tests:
London and Bananas
London and Oranges
London and Apples
London and Pears
Cambridge and Bananas
Cambridge and Oranges
Cambridge and Apples
Cambridge and Pears

... but they could be queried together using ORs.

You arrange your criteria like this:

Excel Workbook
	R	S
1	Store	Fruit
2	London Store	Apples
3	Cambridge Store	Bananas
Sheet

... and just keep adding the combinations chosen to the list.

 

[gr6au]

Thanks for your reply Glenn. 64 combinations is correct. For example you could have Cambride AND Bananas AND Apples AND Pears.

Richard

 

[GlennUK]

Thanks for your reply Glenn. 64 combinations is correct. For example you could have Cambride AND Bananas AND Apples AND Pears.

Richard

WRONG! That is not a valid combination for 2 fields. You are phrasing the query as you would an English sentence, but logically it makes no sense. A field cannot be Bananas and Apples and Pears at the same time. Your actually query would be:

( Cambridge AND Bananas) OR

( Cambridge AND Apples ) OR

( Cambridge AND Pears )

Can you now see how my solution would actually work?