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

 

[Andrew Poulsom]

Can't you put some VBA code in the ComboBox's Change event procedure. There's an example here:

http://www.mrexcel.com/forum/excel-...es-page-field-combobox-another-worksheet.html

 

[rlodge5]

Thanks, am sure I could if I were VBA conversant but am not so can't

 

[Andrew Poulsom]

Did you look at the link I posted? In Excel 2010 and above you can use a Slicer.

 

[rlodge5]

I did and am now furiously trying to understand the VBA tutorial on line in MSDN!
I did read about slicers, unfortunately I've got 2007 but the slicer would be great!

 

[Andrew Poulsom]

Where is your ComboBox from? What are the names of the pivot tables and on which sheets are they? Also what are the names of the report filters?

 

[rlodge5]

combox is on the dashboard - a separate sheet in the workbook, pivot table is just pivot table 1 /2/3 etc and pivot table is on a sheet called 'val pivot'
i haven't given the report filters any names so it has taken the field names - 'Supplier'

 

[Andrew Poulsom]

What sort of ComboBox is it? Data Validation, Form Control or ActiveX? If the former which cell is it in; if the latter what's its name?

 

[rlodge5]

its a form control and its on sheet 2 at c3 but floats on top of c3 so does overlap some other cells

 

[Andrew Poulsom]

Does this work for you when assigned to your ComboBox?

Sub Pivots()
    With ActiveSheet.DropDowns(Application.Caller)
        Worksheets("val pivot").PivotTables(1).PivotFields("Supplier").CurrentPage = .List(.ListIndex)
    End With
End Sub