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 cAsked:
Who is Participating?
 
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
 
KoenChange and Transition ManagerCommented:
a worksheet/workbook with some data and what you want to achieve, is always helpful...
0
 
Saha cAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
Saha cAuthor Commented:
In same user form sheet we can get those values  with graphs after clicking submit button ?
0
 
Saha cAuthor Commented:
rather than going back to data sheet ...
0
 
Saha cAuthor 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 cAuthor Commented:
Please i need with VBA code only, please help....
0
 
Saha cAuthor 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 cAuthor 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 cAuthor Commented:
please find attached updated sheet
newDemo.xlsm
0
 
Saha cAuthor 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 cAuthor 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 cAuthor 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 cAuthor 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 cAuthor Commented:
In Start_YYWW drop down how to bring calendar app for drop down list
0
 
Saha cAuthor 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 cAuthor 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 cAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.