Jinpachi M.
03-02-2021, 03:31 PM
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!
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!