Hello,
I am trying to create a button or a tick box that should trigger or set certain filters.
For instance, it should be possible to select January, March and November on another tab than where the actual filters are placed, the data is then filtered according to those 3 choices.
Is this possible? Any tips?
Thanks!
Here is an example I wrote to possibly match what you need.
There may be a much shorter way to code it, but this is the first thing I wrote.
Please Login or Register to view this content.
Click on the * icon if this post has been helpful.
Hi,
Thank you very much for your reply. Impressive work! Unfortunately my Excel skills are far too restricted for me to understand any of this
The months are of course only an example. In the spreadsheet I'm working with the filtering is done on a variety of things. So a few questions:
1. How do I insert this code on my sheet?
2. Is it easy to change so it applies to my filters?
3. When I tick a box, it "jumps" to the other tab. Is it possible to change that so it stays in the same tab?
Cheers!
You're welcome.
Maybe the best way would be if you were to put a good sample that covers what you need, and I can work through the coding, then re-post the workbook when I'm done and explain it all then.
1. When the spreadsheet you have is open, press Alt+F11 and the VBA editor will open. Click 'Insert', and choose 'Module', that module is were the code gets copied to.
2. This answer all depends on what all the filters will be. As I stated above, the best way would be for me to get a rather complete sample to work with.
3. Yes, it is very possible, and I will fix that in the next one I post.
Last edited by Xer2; 07-16-2012 at 10:29 AM. Reason: Re-posted with answers to questions.
Hi,
Attached you have the spreadsheet IŽ'm working with. That will probably clarify things a bit more, and I should have attached this from the start. Sorry about that.
Anyway, on the second tab ("KPI's") is where I want to be able to apply/choose/select filters that are on the first tab "Project Details". The filters and their aoptions (selections) that are relevant to have are:
- GIT Unit
- Stage
- Sub Portfolio Area
- Strategic SG
Thanks ever so much!
I'll be honest with you, this will take some time. But I will work on it.
I have it to what I think you need, but I can't post all the code here. There are several buttons, sheets, and subs that all have their own code.
I am attaching my finished product. Please let me know how it works for you.
Last edited by Xer2; 07-17-2012 at 04:48 PM.
Xer2, you've done a fantastic job! Impressive! A few questions if you have time,
1. How do I copy this over to my original worksheet?
2. Is there any way of moving the boxes around?
Cheers!
1. Open the KPI's Test.xlsm file and your original separately and put both of them in design mode.
2. While in the Test file and holding Ctrl, select all the labels(4), buttons(11), and list boxes(4)
3. Press Ctrl+C to copy all the items
4. Paste all the items on the worksheet of your choice in you original
5. You need your file in design mode to move the items to where you want them
6. Press Alt+F11 to open the VBA editor
7. Copy all the code from [Test] KPI's sheet to [Original] KPI's sheet
8. Copy all the code from [Test] Module 1 to [Original] Module 1
If there are anymore questions, please feel free to ask.
Hi,
I can't seem to get it to work? I manage to copy all the labels and buttons but the list boxes are empty of the choices after copying.
Also, nothing happens (perhaps due to the above?) when I copy the code from and to the different sheets.
Any ideas?
Thanks!
Sorry, I missed one thing.
9. Copy all the code from [Test] ThisWorkbook to [Original] ThisWorkbook
or just place this code in [Original] ThisWorkbook VBA
Then save, close, and reopen the workbook.Please Login or Register to view this content.
Let me know how it goes.
Last edited by Xer2; 07-19-2012 at 10:36 AM.
Hi,
Unfortunately I can't get it to work. When I paste the code according to your instructions, it seems as if it's referring to the wrong sheet and I get a debug message. Attached you have two images of what that looks like.
Excel 1.jpgExcel 2.jpg
Also, the tick boxes are not appearing (probably due to the above).
Any thoughts?
I've sent you a private message. Please check it.
Here is the original file without the data.
Thanks!
Ok, here you go. If you use the attached file and add your data back to it, it should work just fine.
The code didn't work in your file because when the button, list boxes, and labels were copied over, it didn't keep the objects' names intact; so the code had nothing to reference.
The objects are now renamed correctly and the code is once again working.
Please let me know if there are any additional issues.
Hi!
Thanks for great work. We're almost at the finish line now. It's almost perfect, but I think the boxes are referring to the wrong rows, e.g. "Strategic SG" box has values of "Levels" (1, 2 etc.) and the "Sub Portfolio Area" has values of "Strategic SG" (SSG 1, 2 etc.).
Easy fix?
Cheers!
Sorry about that, try this one.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks