Pivot tables and using combox box selections as a report filter

[rlodge5]

Hi, I need to use a combo box value in a report filter for a series of pivot tables.
I have a combo box with a drop down list of suppliers on the dashboard front sheet and want to be able to select one of the values and then update all the pivot tables/charts with the report filter using that value selected.

Any ideas gratefully received!
Thanks
Richard

 

[rlodge5]

how do I assign it to the combo box ? put in one of the controls? or run a small macro on its own

 

[rlodge5]

Thanks ANdrew, apologies for my naevity - how do I assign that to the combo box?

 

[Andrew Poulsom]

I didn't see your previous message, sorry. Right click the ComboBox and choose Assign Macro.

 

[rlodge5]

Thanks - unfortunately when I right click, there isn't an option to choose 'Assign Macro'.
I get:
Cut
Copy
Paste
-----
Properties
View code
-----
Combo box object>
-----
Grouping
Order
-----
Format control
Have macros been disabled or something?

 

[Andrew Poulsom]

That means it's an ActiveX ComboBox (not Form). If you choose View Code you will get a procedure stub. Copy it and paste it into a reply here.

 

[rlodge5]

OK, here you go:

Private Sub ComboBox1_Change()


End Sub

 

[Andrew Poulsom]

Try:

Private Sub ComboBox1_Change()
    Worksheets("val pivot").PivotTables(1).PivotFields("Supplier").CurrentPage = ComboBox1.Value
End Sub

 

[rlodge5]

The VB is now showing error 1004 - Application-defined or object-defined error

 

[Andrew Poulsom]

Can you record a macro while setting the current page on that pivot table and post the VBA code please? I did test the code before posting it.

 

[rlodge5]

Hi, Sorry Andrew, for my ignorance again on macros - what do you mean by 'setting the current page on the pivot'???