Dependant ActiveX comboboxes filtering multiple pivot tables Issue

[Jinpachi M.]

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, but I still have some problems due to my limited knowledge. I would like to learn and would really appreciate your help!

My current goals are as follows:

make the macro I wrote more stable (I am a beginner with VBA and every tip is always welcome!), because sometimes, if I delete the names of the products filtered in the combobox itself, they might even disappear;
being able to clear the comboboxes and make the pivot tables return as before;
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...);
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 my code and my attached sample file.




Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Sheets("DB_Joined").Visible = True
Application.ScreenUpdating = False
Sheets("Widgets").Select
ActiveSheet.PivotTables("Widgets").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
Sheets("Cronology").Select
ActiveSheet.PivotTables("Cronology").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
Sheets("Department").Select
ActiveSheet.PivotTables("Department").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
Sheets("Managers").Select
ActiveSheet.PivotTables("Managers").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
Sheets("Engineers").Select
ActiveSheet.PivotTables("Engineers").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
Sheets("Notes").Select
ActiveSheet.PivotTables("Notes").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
Sheets("Combo").Select
End Sub

Thank you very much in advance!

[Jinpachi M.]

Hello everyone,
I hope I am not too late to apologize. Unluckily yesterday I couldn't find the forum rules and couldn't think about explaining that I already opened a similar thread here:
Help with three dependent ActiveX comboboxes? (excelforum.com) (https://www.excelforum.com/excel-programming-vba-macros/1342763-help-with-three-dependent-activex-comboboxes.html)

I did not mean to lack of respect to anyone. The fact is that I am not that expert in using forums and the need to receive help made me act without realizing I was actually breaking a rule.

I am sorry and I am willing to do whatever it takes to fix my mistake.

[SamT]

Just to clean up your Macro. I haven't looked at your upload yet.


Option Explicit

Dim ProductPivotSheets As Variant

Sub RunOnce()
'This sub Initializes all global variables.
'run this before any other subs
ProductPivotSheets = Array("Widgets", "Cronology", "Department", "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)).PivotField s("Product").CurrentPage = CPName
Next i

Sheets("DB_Joined").Visible = True
Application.ScreenUpdating = True '<------------ Important: Screen Updating is persistant

End Sub

[Jinpachi M.]

Hello SamT,
first of all thank you so much for understanding and helping me.

I know that you haven't looked at my file yet, but out of curiosity I read and tried your macro... and I must admit that what you did amazed me. Not only your macro "cleaned" the mine, but also taught me something I wouldn't have been able to achieve just by myself!
I would be pleased if you found the time to check my file, too, because surely I could learn something more from experts like you!

[Paul_Hossler]

Hello everyone,
I hope I am not too late to apologize. Unluckily yesterday I couldn't find the forum rules and couldn't think about explaining that I already opened a similar thread here:
Help with three dependent ActiveX comboboxes? (excelforum.com) (https://www.excelforum.com/excel-programming-vba-macros/1342763-help-with-three-dependent-activex-comboboxes.html)

I did not mean to lack of respect to anyone. The fact is that I am not that expert in using forums and the need to receive help made me act without realizing I was actually breaking a rule.

I am sorry and I am willing to do whatever it takes to fix my mistake.


No problem

Don't worry about it

We're here to help, we just like to know if you already have an answer in another forum (although everyone knows that VBAexpress is the best :yes)

[Jinpachi M.]

Hello Paul,
you are right: I actually received an answer from someone who kindly advised me to use slicers instead of comboboxes. It is a good solution, because it actually shows what I would like to accomplish, but it causes me a couple of problems:

- 1: in reality I have to manage thousands of products and honestly I think that having a huge slicer with so many buttons risks to be too much dispersive...;
- 2: I decided to give the possibility to open PDF files associated with the results of the filtered products info obtained and, if I am not mistaken, it is not possible to obtain such a function with slicers (or their own filters).

In my opinion dependant comboboxes still look like what I need, but if you have an alternative solution I'm open to anything. ��

Thank you for letting me clarify!

[Paul_Hossler]

Personally, I'd still go with slicers

Adding a helper 'Starts With' column makes it easier

Play with the attachment. It could use a lot of polishing, but show the idea

28057

[Jinpachi M.]

Hello Paul,
thank you, it's an interesting solution and I like it. I will definitely try to learn it for myself, but another problem of mine is the fact that I will have to share this work with other people, who would surely not find it so intuitive to use. I would stick to a more "classical" design to facilitate the acceptance of this work.

Out of curiosity, may I ask you if there's a way to open a PDF file with the results of filtered slicers?

[Jinpachi M.]

Hello everyone,
I have the doubt that my last post might be misunderstanding, so I would just like to clarify that by "classical design" I meant "keeping the comboboxes": they're definitely harder to make, but easier to use and understand, especially for someone who isn't so used to computers. ;)

About the macro for opening PDF files, if it's a problem don't bother making it: I didn't actually ask it in the first place, because before asking help here I would like to try to write a new code on my own (hoping I will manage to do it!).

As always, thank you for your kindness!

[Jinpachi M.]

Hello everyone,
I'm sorry to bump this, but I would almost like to know if it is possible to filter the "Product" and "Process" tables together with all the others.

I would really like to finish this work, but I've been stuck for months and I confess I'm starting to feel discouraged.