Dependent ActiveX comboboxes filtering multiple pivot tables

[Efreet61]

Hello everyone,
I am using Office 365 and I made a dashboard which is built from several pivot tables and is filtered by some slicers and three dependant ActiveX comboboxes. The first combobox should filter the "Product" field, the second the "W.O." field and the third the "Operations" one.

Currently I almost managed to complete the first combobox, thanks to the very kind help I got from these forums:

Help with three dependent ActiveX comboboxes? (excelforum.com)
and
Dependant ActiveX comboboxes filtering multiple pivot tables Issue (vbaexpress.com)

but I still have some problems due to my limited knowledge and I fear that my question is impossible to answer, given the helpful (but not complete) answers I got...
This literally makes me feel stuck and frustrated, because I have been working on this project since last November. I would like to learn and would really appreciate your help! Even to know if all of this can actually be done or not!

My current goals are as follows:

1. being able to clear the comboboxes and make the pivot tables return as before (unfiltered);
2. filter the "Product" and "Progress" tables, located in their respective sheets (it seems like I am not able to filter tables which do not have the "Product" field in the pivot filter...);
3. connect (if possible, because it would be a gem!) the slicers to the comboboxes, so that they react whenever they filter a product.

Down below you have the code I received and my attached sample file.

Option Explicit

Dim ProductPivotSheets As Variant

Sub RunOnce()
'This sub Initializes all global variables.
'run this before any other subs
    ProductPivotSheets = Array("Widgets", "Cronology", "Departments", "Managers", "Engineers", "Notes")
End Sub

Private Sub ComboBox1_Change()
Dim CPName As String
Dim i As Long
CPName = Me.ComboBox1.Value

Application.ScreenUpdating = False
    For i = LBound(ProductPivotSheets) To UBound(ProductPivotSheets)
        Sheets(ProductPivotSheets(i)).PivotTables(ProductPivotSheets(i)).PivotFields("Product").CurrentPage = CPName
    Next i
   
    Sheets("DB_Joined").Visible = True
Application.ScreenUpdating = True '<------------ Important: Screen Updating is persistant

End Sub

Obviously, if I ever receive an answer in the other forums, I won't hesitate to share it here.
I hope you understand that I did not write here just to save time, but because now it has become a necessity of mine.

Thank you in advance
Efreet61

 

[Luke M]

Hi Efreet. Sorry to see that you've been struggling with this for awhile, with not response. In truth, I have accomplished something like this for a client, but it took awhile to figure out so perhaps the complexity of the problem has deterred others from responding.

That said, I'll do my best to try and get you up and running.

As your file has several sheets and slicers going on, I'm hoping you can walk me through in small steps what we need to do? It looks like although you have many PivotTables, they all share a common data source? As in, your slicers are connects to all the PTs? If yes, that will make things a bit easier, as you can have the Dropdowns just toggle the slicers, rather than having to loop through each PT. That said, is there a reason you didn't want to use slicers for Prod, WO, and Operations? Is it that there are just too many results to display cleanly? Using all slicers would be convenient in that they would automatically all be dependent on each other.

 

[Efreet61]

Hi Luke M and thank you for your kindness!
Yes, all my PivotTables are generated from the "Joined Database" and their slicers are connected (if some aren't, I simply forgot to do it, my bad...) to each PivotTable. The idea of toggling the slicers is very interesting!

Also yes, you understood perfectly my problem with the slicers for Prod, WO, and Operations:

1. in reality, I have to manage hundreds of Products and I can't afford to use hundreds buttons;
2. this file would not be used just by me, but by other people who are not so familiar with computers and Excel... I think that the design of a combobox, despite being way harder to make, in this case is more intuitive;
3. the third reason is the fact that I would like to add with a button the possibility to open a PDF file corresponding to the filtered results of the comboboxes. I must admit that I don't know how to do it with slicers, though. If it's possible and you have an idea I'm open to any advice!

 

[Luke M]

Let's see how far we get with this. I setup some Named Ranges to feed the 3 combo boxes. Unfortunately, it looks like ActiveX doesn't actively read from a range, so I had to come up with a way to "refresh" the pulls. Note the new code module that's been added.

I also setup 3 slicers corresponding to the 3 dropdowns. For now, they are sitting on the Product sheet, but they can really be anywhere. I've amended the ComboBox_change event code so that the appropriate slicers get toggled. Note that you don't want to create a circular type reference, so the Slicer doesn't change the same PT that is feeding the combo box (otherwise once you made a choice, you would never be able to go back).

I've not done much extensive testing on this yet, so please check things out.

 

[Efreet61]

Hi Luke M,
first of all, thank you very much. What you did is very interesting!
I tried your file: the "Product" combobox seems to work perfectly, but the other two both get the error in the image. Once filtered the combos, I also tried the slicers (those set in "Dashboard" Sheet), but they too don't seem to work, yet.
Finally, I obviously saw your three slicers in the "Product" Sheet: once connected to each other they work like a charm, but unluckily the actual situation confirms my doubts I posted in my previous comment.

If I may ask you a question about the comboboxes: is there a right way to clear their content, whenever you want to see the charts unfiltered in the dashboard? I just deleted the content in the combos, but honeslty I don't know if it's the safest way to do it.

Again, thank you so much! I am looking forward to hearing from you!

 

[Luke M]

Finally, I obviously saw your three slicers in the "Product" Sheet: once connected to each other they work like a charm, but unluckily the actual situation confirms my doubts I posted in my previous comment.

Can you elaborate on what you mean by this part?

To your other question, yes, clearing/deleting the combobox should be just fine. I built a section in the code that checks for blank values = unfilter.

 

[Efreet61]

Also yes, you understood perfectly my problem with the slicers for Prod, WO, and Operations:

1. in reality, I have to manage hundreds of Products and I can't afford to use hundreds buttons;
2. this file would not be used just by me, but by other people who are not so familiar with computers and Excel... I think that the design of a combobox, despite being way harder to make, in this case is more intuitive;
3. the third reason is the fact that I would like to add with a button the possibility to open a PDF file corresponding to the filtered results of the comboboxes. I must admit that I don't know how to do it with slicers, though. If it's possible and you have an idea I'm open to any advice!

Hello Luke M,
I meant to say that these are my problems with slicers.

 

[Efreet61]

To your other question, yes, clearing/deleting the combobox should be just fine. I built a section in the code that checks for blank values = unfilter.

P. S. Ok, thank you!

 

[Efreet61]

Hello,
out of curiosity I tried to insert a timeline, just to see how it would work. Obviously I did not dare to touch Luke M's codes, being they way beyond my knowledge, but in my ignorance I think it could work fine with the other slicers and comboboxes.

I just wanted to share this little detail.

As always, thank you for your help.