How to use a list box to select pivot table page fields

Skysurfer

Board Regular
Joined
Apr 19, 2004
Messages
194
Hi,

Is there a way to loop through all the page fields in a pivot table and mark them true/false based on selections in a multi select list box?

I have a dynamic list box on a user form that contains all the page fields, which are dynamic. What I need is to have the pivot table fields tie to whatever selections the user makes in the list box.

Here is the test code I have for changing the page fields:

Code:
Sub Macro5()

    With ActiveSheet.PivotTables("PVT_Chart01_SeriesData").PivotFields("Product")
        .PivotItems("Bass Amps").Visible = False
        .PivotItems("Cobalt").Visible = False
        .PivotItems("GTR Amps").Visible = True
        .PivotItems("Guitar Parts").Visible = True
        .PivotItems("Guitars").Visible = True
        .PivotItems("PAs").Visible = True
        .PivotItems("Speaker Parts").Visible = True
        .PivotItems("Speakers").Visible = True
        .PivotItems("(blank)").Visible = True
    End With

End Sub

I guess for a loop (if that is what works), I need to increment both the page field number and the list box index number???

Thanks,

Lawrence
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Threads
1,108,727
Messages
5,524,477
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top