How to filter a pivot table with an ActiveX combo box set in another Sheet?

[AKing_1st]

Hello everyone,
I am having a lot of problems in finding some material which could help me learn how to filter a pivot table with an ActiveX combo box set in another Sheet and I was hoping in some help of yours.
I would like to make a dynamic combo box, which updates automatically whenever its source changes (e.g. when I insert a new product).

If necessary, this is the link for a very simple example of mine: Example for combo box.xlsx

Thank you in advance!
AKing_1st


P.S. What I would really like to accomplish is having three dependant combo boxes, but I fear that would be asking too much. Feel free to help me with just one combo box, if you prefer. I'll try doing the rest.

 

[GlennUK]

Explain why you don't want to use Slicers?

 

[AKing_1st]

Explain why you don't want to use Slicers?

Hello GlennUK,
thank you for asking.
I don't want to use slicers, because I'm making a dashboard in which I can filter my products. In reality I have to manage thousands of products and I can't use thousands slicers. Furthermore, I would like to be able to open a pdf file associated with the results of the combo boxes. I know that I'll need VBA for this, too, but I hope it will give me less problems than this one.

Let me know if I expressed myself well, please. English is not my native language. ?

 

[GlennUK]

Look at this before going any further:

 

[AKing_1st]

Look at this before going any further:

Hello GlennUK,
thank you, it's very original, but this time it's not the ideal solution for me.
I'm sorry.

 

[AKing_1st]

Hello everyone,
a month ago I opened this thread, but due to my minimal knowledge about the topic I ended with not giving you the necessary info...
Now, after some studying, I think I realized what I need to complete my work, which is a dashboard built from several pivot tables and filtered by some slicers and three dependent 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:

1. 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...);
2. being able to clear the comboboxes and make the pivot tables return as before (unfiltered);
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 and this is my sample file: Example Dashboard with comboboxes.xlsm .

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 

End Sub

I hope you can help me!

Cheers
AKing_1st