User Form Check Boxes and Adv Filters

gr6au

New Member
Joined
Nov 23, 2010
Messages
21
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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
RS
1StoreFruit
2London StoreApples
3Cambridge StoreBananas
Sheet



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

gr6au

New Member
Joined
Nov 23, 2010
Messages
21
Thanks for your reply Glenn. 64 combinations is correct. For example you could have Cambride AND Bananas AND Apples AND Pears.

Richard
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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?
 

Forum statistics

Threads
1,144,389
Messages
5,724,073
Members
422,534
Latest member
ThiccNugg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top