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

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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. ;)
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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. 😉
 

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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 .
wink.gif


VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,722
Members
415,853
Latest member
Newlife72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top