PDA

View Full Version : filter pivot table with list box



tommy1234
03-08-2011, 07:34 AM
Hello
i built an excel sheet that give the user the ability to see data in a visual way.
i created 3 list box in order to filter the pivot table (my pivot table is around 100 rows and 35 columns).
i need to filter each list box according to the one i filtered before- every time i choose list box the one after will show less options.
i wrote some come but the problem is that it works very slow (CPU around 90%)

Sub BoxChange1()
'translates listbox selections to pivotfield item visiblity
Dim i As Integer
Dim Lb As ListBox
Dim Pt As PivotTable
Dim Pi As String
Dim Ws As Worksheet

Application.ScreenUpdating = False
Set Lb = ActiveSheet.ListBoxes(Application.Caller)
Set Pt = Sheets("Pivot").PivotTables("pivottable1")
Set Ws = Sheets("data")

'loop through listbox and set pivot items
On Error Resume Next
For i = 1 To Lb.ListCount
Pi = Ws.Range(Lb.ListFillRange).Cells(i, 1)
Pt.PivotFields(Lb.Name).PivotItems(Pi).Visible = True
If Lb.Selected(i) = False Then
Pt.PivotFields(Lb.Name).PivotItems(Pi).Visible = False
End If
Next i
Application.ScreenUpdating = False
End Sub



Please help

thanks

tommy1234
03-08-2011, 10:57 PM
please, can someone help me ???