Filter cells based on the details given by user.

Is it possible to filter cells by VBA code?
In one sheet all the tables with filters will be there and in next sheet we have to create one user form window.

In user form window we have to give all the details and if we submit that sheet it should go to first page displays only that data. It has also display the pie chart for the same.

Please help someone....
Saha HAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KoenChange and Transition ManagerCommented:
a worksheet/workbook with some data and what you want to achieve, is always helpful...
0
Saha HAuthor Commented:
This is the attached format for that.
In first sheet it consists the details with list and in second sheet userform.
In second sheet we have to give the details what we want and after submitting it has to go to first sheet and display the result .
And it also display the pie chart or barchart for the same.

Is it possible?
Thanks in Advance....
dummyreport.xlsm
0
Roy CoxGroup Finance ManagerCommented:
To be honest it seems like a waste of time. Why not simply instruct the users to use AutoFilter on the data sheet?

As it is your workbook requires code to load the ComboBoxes and then further code to filter the data - this is automatic in Excel's AutoFilter and allows for additional items being added which your code does not.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Saha HAuthor Commented:
In same user form sheet we can get those values  with graphs after clicking submit button ?
0
Saha HAuthor Commented:
rather than going back to data sheet ...
0
Saha HAuthor Commented:
Or any other procedure is there for that getting values ?
0
Roy CoxGroup Finance ManagerCommented:
It's not actually a UserForm, but the best way would be to create a PivotTable with a PivotChart

Overview of PivotTables and PivotCharts
0
Saha HAuthor Commented:
Please i need with VBA code only, please help....
0
Roy CoxGroup Finance ManagerCommented:
Your current code loads comboboxes repeatedly.

Have a look at this, I'm not sure what the third combobox is supposed to filter. Let me know if it is what you mean and I'll tidy up the code.
dummyreport.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saha HAuthor Commented:
Thank you..
after submitting it is not going to first page to show the results.
0
Rob HensonFinance AnalystCommented:
You can also use Slicers to apply filters to the data. The slicers can be on the same sheet as the chart rather than with the data but as Roy mentions, that would then effectively be a Pivot Chart.

Not sure if the Chart will automatically hide filtered data like it does for manually hidden rows/columns.
0
Saha HAuthor Commented:
OK thank you sir,

One modification in the sheet, instead of doing in second sheet can we do it in first sheet only everything.
Please help me...
Sorry for disturbing again and again

And thanks in advance..
demoreport.xlsm
0
Roy CoxGroup Finance ManagerCommented:
I've demonstrated how to write the code correctly. You now need to adapt that code for each ComboBox in the sheet that you want to use. Place the code into WorkSheet module of the specific sheet.

Add Excel VBA Code to a Worksheet Module

The code is Event code, and will run automatically when a specific action occurs in  the worksheet For example, if you type in a cell, and press the Enter key, the worksheet has been changed. This would trigger the Worksheet_Change event. Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

Copy the code that you want to use
Select the worksheet in which you want the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.Where the cursor is flashing, choose Edit -> Paste

It makes even less sense to use ComboBoxes instead of the built-in Filter dropdowns on the same sheet as the data. Alternatively use Slicers as Rob suggests

Use slicers to filter data
0
Saha HAuthor Commented:
please find attached updated sheet
newDemo.xlsm
0
Saha HAuthor Commented:
OK and for apply filter, save filter ,reset filter , save as , Print , last updated information and visitors buttons how to apply code for that?
0
Saha HAuthor Commented:
And also for some drop down buttons like submitter we don't know the exact value it depends upon the user right?
How to  do that ?
0
Roy CoxGroup Finance ManagerCommented:
The filter is applied when the combobox is changed automatically.

OK and for apply filter, save filter ,reset filter , save as , Print , last updated information and visitors buttons how to apply code for that?

This was not in the original question!
You cannot save a Filter

I've applied code to do the other commands
demoreport.xlsm
0
Saha HAuthor Commented:
Thank you.

Private Sub ComboBox4_Change()
Sheet1.ListObjects("Table1").Range.AutoFilter Field:=10, Criteria1:= _
                                                  Me.ComboBox4.Value
End Sub


This code is not working....
0
Saha HAuthor Commented:
Copy pasting the same code is not working properly..
0
Roy CoxGroup Finance ManagerCommented:
You need to make sure that the code refers to the correct column and the combobox contains the correct criteria.

I'll repeat again - you are making hard work out of a simple task!!
0
Saha HAuthor Commented:
In Start_YYWW drop down how to bring calendar app for drop down list
0
Saha HAuthor Commented:
Please i want to modify the code accordingly, because every time we can't insert data in dropdown list  and in table right? If user wants to insert data again we need to change the code and we have add in dropdowns .
So, whatever we insert data in table it should reflect automatically in respective dropdowns and for the date it should take calender from the jan 2017 to dec 2020 .
Please is it possible.....
Please i need help......
0
Roy CoxGroup Finance ManagerCommented:
As I said AutoFilter or Slicers are most suitable and will do what you want.

VBA will not do it automatically and will be less efficient.

I have more than answered the original question and you will not listen to advice.
0
Saha HAuthor Commented:
OK sorry sir..

But i have assigned with that task with VBA code by my manager. So i have to complete that.
 And i am not aware of that slicers.
0
Roy CoxGroup Finance ManagerCommented:
I've given you a link to Slicers with an example.

You need to speak to your Manager and tell him what I have advised.
0
Saha HAuthor Commented:
Noo he will not listen, he wants in that manner only...
0
Roy CoxGroup Finance ManagerCommented:
Then tell him from me if he knows better to get on with it or pay for a developer to do it. As I said the original question has been answered but now the further requirements in my opinion move from being a simple Forum Question to being a Gig (paid for project)
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.