• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter Multiple Tables with VBA Macro Button

J

jiajia97

New Member
Hi,

May I onow hoe to use VBA Macro Button to filter multiple tables at the same time with each tables have one common category among them. Basically, I have 7 tables need to be filtered at the same time and i wish to filter it by creating a VBA Macro Button. Is it possible?
 
Hi Narayan,

I have read the link you share. Unfortunately, I am still not understand about how it works. Hence, I am attaching my workbook sample here and hope you may assist me on this matter. Basically, I hope I can create different macro button for each PIC and and the vba can filter the tables based on respective PIC.

Thank you.
 

Attachments

  • sample.xlsx
    42.6 KB · Views: 11
Hi,​
​
as there is no table in your attachment … Better is to convert each range to an Excel table,​
attach the new workbook but saved as binary format .xlsb​
then explain at least how do you want to filter, what is your scenario ?​
​
 
Hi ,

See the attached file.

Enter A , B , C or D in cell D1 and then click the button ; all the tables will be filtered on the selected PIC value.

Clear the cell D1 and click the button to remove the autofilter on all the tables.

Narayan
 

Attachments

  • Sample Test.xlsm
    50.8 KB · Views: 19
Hi,

Thank you for your reply. Based on the workbook, there 4 tables with different PIC. Hence, may I know is it possible to create different buttons for respective PIC, and when I click the button, it may straight away to filter 4 tables simultaneously instead of I need to filter it?

For example, there is different PIC in each tables e.g. A, B, C, D. And I wish to create 4 buttons which represent A, B, C, D and when I click one of them, it may straight away filter the 4 tables by just showing the data related to the respective PIC.

I hope it will make you clear. Thank you for your assistance.
 
Hi ,

I have done exactly the same thing ; instead of having 4 buttons for the four values in the PIC field , I have only one button , and the value you want to filter on is entered in cell D1.

The advantage of this approach is that nothing needs to be changed even if you add another 10 values in the PIC field.

Narayan
 
Hi,

I am sorry as I just realize It is needed to fill in the value in D1. It is really nice work but I am not sure it is suitable to apply this coding in a excel dashboard or not. As the PIC name is long and it takes time to fill in one by one.
 
Hi ,

In that case , we can have a Data Validation set up in cell D1 , so that the user only has to select the required value.

Having a button whose caption is long will make the button either long or big to accommodate all of the text.

If you can upload the workbook with the 4 values in the PIC field , I can make the Data Validation in cell D1.

Anyway see the revised version of the workbook with 5 buttons for the 4 values and reset.

You can enter the long text in each button caption if you want.

Narayan
 

Attachments

  • Sample Test.xlsm
    52.7 KB · Views: 16
NARAYANK991 said:
Hi ,

In that case , we can have a Data Validation set up in cell D1 , so that the user only has to select the required value.

Having a button whose caption is long will make the button either long or big to accommodate all of the text.

If you can upload the workbook with the 4 values in the PIC field , I can make the Data Validation in cell D1.

Anyway see the revised version of the workbook with 5 buttons for the 4 values and reset.

You can enter the long text in each button caption if you want.

Narayan
Click to expand...
Hi,

I am so impress about your work. May I get the steps to create the VBA macro button? Besides, is it possible the macro buttons' name is different with the PIC column refer. For example, the PICs are A, B, C, D and I would like assign the A,B,C,D respectively to different category e.g. Dept OPI, Dept WP and so on....
 
Hi ,

Creating the buttons is just a matter of using the Excel menus.

Click on the Developer tab.

Click on the Insert Controls button on the Ribbon.

Click on the first control under the section labelled Form Controls , which is a Button.

Click on the worksheet where you wish to place this button and size it to what size you think is suitable ; of course the size can always be changed later by just clicking on the button and dragging any one corner.

Once you release the resizing action , the Assign Macro dialog box will be shown , with the name of the macro already entered by default ; if this default name is acceptable , click on New , otherwise enter a name of your choice and click on New.

The Visual Basic editor will be displayed for you to enter the code for the new macro.

------------------------------------------------------------------------------------------------------------------------------------------------------------------

If the PIC values are A , B , C and D , and the Button captions are different , then the code will have to be changed to take this into consideration.

If you can upload your workbook , these changes can be made.

Narayan
 
NARAYANK991 said:
Hi ,

Creating the buttons is just a matter of using the Excel menus.

Click on the Developer tab.

Click on the Insert Controls button on the Ribbon.

Click on the first control under the section labelled Form Controls , which is a Button.

Click on the worksheet where you wish to place this button and size it to what size you think is suitable ; of course the size can always be changed later by just clicking on the button and dragging any one corner.

Once you release the resizing action , the Assign Macro dialog box will be shown , with the name of the macro already entered by default ; if this default name is acceptable , click on New , otherwise enter a name of your choice and click on New.

The Visual Basic editor will be displayed for you to enter the code for the new macro.

------------------------------------------------------------------------------------------------------------------------------------------------------------------

If the PIC values are A , B , C and D , and the Button captions are different , then the code will have to be changed to take this into consideration.

If you can upload your workbook , these changes can be made.

Narayan
Click to expand...
Hi,

Thank you for your reply. Attach is the workbook sample which is the one you edited before. However, for this time, I hope to assign the PIC with the group as listed before:

A: OPI
B: AOM
C: WP
D: OEMS
E: AE
 

Attachments

  • Sample Test (1).xlsm
    52.1 KB · Views: 4
NARAYANK991 said:
Hi ,

See the attached file.

Narayan
Click to expand...
Hi,

Thank you for the reply. For the last question, may i know how can i add other value in the case? For example, in Case A, my value is "OPI", how can I add another Case "Overall" that value cantain all e.g "OPI","AOM" and so on...
 
Hi ,

But why would you want a value such as Overall which represents all the other values ?

Filtering on all the values is as good as not applying a filter.

Narayan
 
NARAYANK991 said:
Hi ,

But why would you want a value such as Overall which represents all the other values ?

Filtering on all the values is as good as not applying a filter.

Narayan
Click to expand...
Hi,

Yes. That is one of my question. I am sorry about not making it clear.

1. Case Overall: which I can remove all filter and show all data.
2. Another Case: add another value so that it will filter both values at the same time.
 
Hi ,

I have earlier said that the button which does not have any text on it is the RESET button ; clicking that button removes a filter.

Is this your last request ?

Please mention what all combinations are possible ; will there be only two combinations possible (e.g. A or B , A or C ,....) or can we also have 3 or even 4 combinations possible (e.g. A or C or D or E) ?

Narayan
 
NARAYANK991 said:
Hi ,

I have earlier said that the button which does not have any text on it is the RESET button ; clicking that button removes a filter.

Is this your last request ?

Please mention what all combinations are possible ; will there be only two combinations possible (e.g. A or B , A or C ,....) or can we also have 3 or even 4 combinations possible (e.g. A or C or D or E) ?

Narayan
Click to expand...
Hi,

Noted for the reset button. I will check again later.

For the current workbook, I have PIC A,B,C,D value. However, when I have new value, I hope it can be added to the case existed. The combinations will be in PIC and it may more than 2 combinations as listed below:

Case A: A+E(new value)
or
Case B: B+F+G
 
You must log in or register to reply here.
Back
Top