Use Checkbox and Combobox as filter

[charleswang]

Hi all,

I am new to VBA and really got problems that i could not solve. Will be appreciated for you kind help!

In the attached, the effect I want is to (separate two things)

1. tick the check box(single or multiple) on the "cover" sheet, and then click the demand button "Basic Information", and then it will lead me to the "basic info" sheet with the column A "Province" filtered.

2. again tick the check box (single or multiple) on the "cover" sheet as the first criteria (corresponding to the column M: "Headquarter" on "companies" sheet), and then click the demand button "Listed Companies", which will show me the userform to let me choose from the combo boxes, and then click "OK" and it will hopefully lead me to the results on "companies" sheet which are satisfied with the 4 criteria (Provinces ticked in the check box, and other 3 in the userform). I may have some thoughts for a single check box (didn't work that nicely though), but really hv no clue for multiple check boxes.

I have tried to create the combo box already but failed to deal with the check box as the first-layer filter (or run with the combo box at the same time)

Sorry to bother but will be more than grateful for your help! Can you kindly give me a hint or give me some examples in the attached so that I can try harder to figure out the rest?

Thanks...a million...lol. Pls see attached as below.

Trial start 20150805.xlsm

[humdingaling]

to make things simpler i went and renamed all your check boxes to the name of the city instead of the default (checkbox 1,2 ....etc)
this makes coding much easier

Please Login or Register  to view this content.

this is the code i got for the filter.
ps for anyone following ...i may have not optimised the array but it works in its current state...please correct me if i have done it incorrectly

does this answer your first question?

[charleswang]

Re,

Thanks a lot, Hum! It works nicely.

I also modified the codes a little so that it can direct me to the "basic info" sheet after clicking the "basic info" button.

I basically understood the underlying logic, but may I ask why do I need to check the auto filter? Why would one already exist? I got a bit confused...

Further, for the second question, I tried to apply similar codes above and reset the array for criteria, but somehow it didn't work...Can you also kindly give me a hint regarding Q2...running the check boxes as filter together with the combo boxes in the userform? i.e., to tick the check box (single or multiple) on the "cover" sheet as the first criteria (corresponding to the column M: "Headquarter" on "companies" sheet), and then to choose from the combo boxes in the userform to get the filtered results. Thanks again!

Pls see attached as below.
Cheers!

Trial start 20150806 v1.xlsm

[humdingaling]

Please Login or Register  to view this content.

i suppose check is probably the wrong word...it should read disable existing autofilter if one exist
if none exist then nothing happens. it just to make sure that there is no autofilter before applying a filter criteria

if an autofilter already on worksheet applying another criteria will not overwrite existing one
so the easiest way to overcome this is just to disable autofilter and start fresh

ill have a look at Q2 tomorrow

[humdingaling]

For Q2
basically use the same code for Q1

Please Login or Register  to view this content.

• change the WS to point to worksheet Companies
• criteria filter field 13 (M)
• extend autofilter from A:I to A:M

[charleswang]

Good day, mate!

It's of great help! Thanks for every effort!

[humdingaling]

glad to help

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.