Hi all,
I am fairly new to VBA; I am trying to filter multiple selections in one column using a VBA Userform with several checkboxes.
I currently possess the code to filter fields in a column, using checkboxes located on the worksheet, but I need to be able to use it with a userform (to act as a "Control Panel" for a large workbook)
I have attached the sample code. Thanks in advance!
Josh
Hi Josh,
Welcome to the forum.
See the attached workbook that I modified.
I only added 3 check boxes...I'm sure you can add more if needed
Regards,
Rudi
Awesome! That is perfect, thank you.
I have another question, regarding this.
Instead of filtering to show items to only show the selected fields. Is there a way to make it so checking the box "hides it"?
Hi,
Try this version...
Outstanding!
I think I might just set the default value to true and have user uncheck items.
Hopefully that wouldn't be too counter intuitive.
I appreciate the help on a short notice; I think I will find myself collaborating here frequently for R&D. You saved so much time. If only I could have been less stubborn and posted this question a couple days ago, I could have saved even more time.
You're a rock star!
Thanks again,
Josh
I do see one minor glitch though. I am wondering if there is an easy solution.
I am wondering if there is a way to ingore criteria that is not listed as a Check Box.
For example:
Fields:
A
B
C
D
Available Check Boxes
A
B
D
As of now if I were to use the "Exclude UserForm" and selected checkbox D, the C field will also be excluded from the filter. (I believe because it isn't listed.)
Sure...will look into it this evening!
I was able to do a fix, by adding a hidden tab with unused checkboxes containing the rest of the filters. That issue is resolved. After integrating this code with my real project, I have been getting another issue though.
I have changed
To
Please Login or Register to view this content.
I have been getting an errorPlease Login or Register to view this content.
Compile Error: Method or data memer not found
with GrpFilter highlighted. Any suggestions?
Hi,
Try this now.
Please note I had assistance from HansV, an MVP in Eileen's Lounge where I am a member. http://www.eileenslounge.com/portal.php
It is his code that resolved the issue I had with the "reverse filter".
Attached
Cheers
Thanks!
Do you have an idea about the compile error?
The file did not compile error when I ran it.
Where is it compiling? Can you send a screenshot or indicate where it errors.
TX
Last edited by Darkenergyz; 04-10-2014 at 02:56 PM.
Ah...so sorry...I forgot to add that little detail.
In order for the dictionary to work, you need to activate the reference library it uses in VBA.
In the VBA editor, click on the Tools menu,
The choose References
In the dialog, scroll down and tick Microsoft Scripting Runtime
The code will work after that.
See the image below:
Got it. I think we are good. If anything changes I will post updates.
Last edited by Darkenergyz; 04-10-2014 at 03:24 PM.
Alright, everything is working on the live version, one slight (But I think it is last!) issue is that it keeps unselecting 0 in one of the collumns.
The data I use comes off an ODC connection from a SharePoint source. So blank cells within the collumn return back as 0s. For some reason the code does not like numerical 0.
Other than that, this is all fantastic.
When you say unselecting 0's in one of the columns, what do you mean?
Do you have a 0 check box that you tick, but then the checkbox unticks?
Please explain.
TX
I do not have a 0 checkbox, but I do have a 0 within the that collumn, because SharePoint translates "blanks" to 0s. For some reason though, the code is unselecting 0s without a checkbox calling it.
I have one more idea! (Then I think we can put this thread to rest) Is there a way to add multiple sort fields to ONE checkbox?
I'll check on those 0's in some trial runs I will do on the sample file.
Regarding the multiple sort on a checkbox....please explain in more detail. If I understand correctly, you want an additional checkbox on the form that if ticked will sort the filtered data in a particular way?
Yes, pretty much what I am thinking is to have a multi page addition to add a series of radio buttons. Essentially when a radio button is selected, it filters(unfilters) 3+ fields at a time.
The idea is to have a detailed category sort(what we have now)and a very high/top level sort.
Another way to think about it is to have "tree" checkbox. A main checkbox that has sub category check boxes within.
I know Nullsoft installable script system supports that. I don't know if VBA can handle that.
Last edited by Darkenergyz; 04-10-2014 at 08:28 PM.
That sounds like a BIG project.
You may want to look at this page for some ideas on a treeview in Excel VBA. It seems like it can handle it.
http://www.jkp-ads.com/articles/treeview.asp
It streams 4 databases to one workbook using Excel's Office Data Connection (ODC). It is a good size project, constantly updating.
That is a great resource, I will read into it this morning.
Appreciate the link.
So at this point, I am wondering if it would be ideal to have all the checkboxes preselected which would make the Worksheet show all content.
Then the User can expand let’s say... by year
2011 2012 2013 2014 2015
So if you were to click 2014 to expand the checkboxes you can deselect column fields under that tree. (R1, R2 and R3)
The years can all be static, they do not have to be dynamic, just having the tree functionality.
(Sorry for the double post)
Hi,
This project your'e envisioning to to expanded (pardon the pun) for me to handle. The link for the treeview should provide a reference point for you to start experimenting with, but to get into the details of what you are asking is going to take too much of my time. Personally, I'd just integrate pivots and autofilter, but that's me...
Maybe another member could assist who has greater knowledge or time available to guide you.
No problem, you have been more than enough help, I think the guide you have posted will work just fine. I know it's difficult to invision without the actual project. Pivots would not fulfil the end goal for this project unfortunatley, or I would be all over that.
Thank you again for all of your help.
I couldn't have done it without you.
It was a pleasure to help... Good luck with the endeavour.
BTW: You are welcome to post for continued assistance during this project, but ensure that your questions are particular to a focused problem or specific challenge you need. I doubt if many will assist if you request entails help in the design of a large project or solution. (Not that this thread implied that!) Cheers.
Rudis,
Per our conversation pertaining the Select All function, here is the workbook.
On another Note: (If needed I can create a new thread)
Is it possible to add a condition to the existing code to create a filter via Pivot Table for a Data Analysis chart based off the current criteria/logic?
The purpose is to view changes visually based on a filter.
Last edited by Darkenergyz; 04-14-2014 at 01:20 PM.
OK...
Attached is the workbook with the form working as I believe you need it. All check boxes initialized on for open.
As for the Pivot table and filer to display on a chart; the filter is stored in an array variable. If you have a pivot table and chart drawn up from the list, we could just apply the array variable to the appropriate field in the pivot table and it should display the records in it and on the chart correctly. (This is all theory though...it will need to be verified in practice.)
Sorry, I don't think I communicated the issue well enough.
It works fine, but when I try to move the code to a different workbook, I get an error
Compile Error:
Method or Data Member not found
with .grpFilter highlighted.
I did a search for grpFilter, I can't find it.
See the image attached to explain the grpFilter error and how to safely move the code to another workbook.
I am an idiot...
Thanks RudiS
We all have moments where we lapse in concentration. LOL
Cheers
This works perfectly. I just wondered whether there was a way to make the filter a contains with filter, rather than an outright filter of the control tag. Usually putting asterisks around the Criterial sorts this out; but not sure how it would work here?
Thanks for the help
Agradeço muito meu amigo... seu trabalho me ajudou demais na minha planilha... desde ontem estava procurando e hoje encontrei. Que Deus lhe dê em dobro... forte abraço...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks