Control Pivot with VBA - Loop

[Emperor]

Hi all,

I currently have this code to control a Pivottable with Listboxes in my Userform;

Dim DraaiTabel As PivotTable
Dim pf As PivotField
Dim LbItem As Long

'Activate correct sheet to be sure
Worksheets("Draaitabel").Activate 'draaitabel = PivotTable in Dutch

Set DraaiTabel = Sheets("Draaitabel").PivotTables("Draaitabel")

Dim i As Integer
Dim count As Integer
Dim lb As MSForms.ListBox
Dim item
Dim pi As PivotItem

'Region (in Dutch "Regio")
Set lb = Me.ListBox10 'Setting
item = "regio" 'Setting
Set pf = DraaiTabel.PivotFields(item)

count = 0
    For i = 0 To lb.ListCount - 1
        If lb.Selected(i) Then
            count = count + 1
        End If
    Next i

If count = lb.ListCount Then 'If the are equal, then show all
     For Each pi In pf.PivotItems
         pi.Visible = True
     Next pi
Else 'If there not equal, turn everything of and only view selected items
     For Each pi In pf.PivotItems
         pi.Visible = False
     
         For LbItem = 0 To Me.ListBox10.ListCount - 1
            If Me.ListBox10.Selected(LbItem) = False Then
                pf.PivotItems(Me.ListBox10.List(LbItem)).Visible = False
            Else
                pf.PivotItems(Me.ListBox10.List(LbItem)).Visible = True
            End If
        Next LbItem
     Next pi
End If

The reason why I have coded it like this with a count is that there are empty field in my source data, when filtering just with the 'else' bit of the code the Empty rows remain in the pivot table next to the selected items.
What i've done is counting the selected items of the listbox and compare this with the number of items, when there equal, then the 'empty' items need to be shown, when there not equal, I only wan't to see the selected items. (so emptyItems.visible = false)

This works, while there is something 'weird' in the last bit, while it works at the time, I dont like the code that much, in a PivotTable it isnt possible to view nothing, so i've put the "Next pi" at the bottom. While I dont know why it works, it does ^^

What I would like is to create something to loop through this code with multiple items. I was thinking to create a string array.

Something like;
item(1) = "regio" 'Setting
item(2) = "city" 'Setting
item(3) = "postal" 'Setting

But I do not know how to start, could somebody help me in the correct direction?

 

[Emperor]

I have created an update in which the loops mostly works, the problem is with listboxes with a lot of information in them, the loop fails (keeps looping)

Dim DraaiTabel As PivotTable
Dim pf As PivotField
Dim LbItem As Long

'activate sheet
Worksheets("Draaitabel").Activate

Set DraaiTabel = Sheets("Draaitabel").PivotTables("Draaitabel")

Dim i As Integer
Dim count As Integer
Dim lb(1 To 8) As MSForms.ListBox
Dim item(1 To 8) As String
Dim pi As PivotItem

item(1) = "Categorie" 
item(2) = "something2"
item(3) = "something3"
item(4) = "something4"
item(5) = "something5"
item(6) = "something6"
item(7) = "something7"
item(8) = "something8"

Set lb(1) = Me.ListBox1 
Set lb(2) = Me.ListBox2 
Set lb(3) = Me.ListBox5
Set lb(4) = Me.ListBox7
Set lb(5) = Me.ListBox8 
Set lb(6) = Me.ListBox10
Set lb(7) = Me.ListBox9 
Set lb(8) = Me.ListBox6

Dim x
For x = 1 To 8

Set pf = DraaiTabel.PivotFields(item(x))

count = 0
    For i = 0 To lb(x).ListCount - 1
        If lb(x).Selected(i) Then
            count = count + 1
        End If
    Next i

If count = lb(x).ListCount Then 'if equal then show all
     For Each pi In pf.PivotItems
         On Error Resume Next
         pi.Visible = True
     Next pi
Else 'when not equal, everything off, then only selected items on.
     For Each pi In pf.PivotItems
        pi.Visible = False
        For LbItem = 0 To lb(x).ListCount - 1
            If lb(x).Selected(LbItem) = False Then
                pf.PivotItems(lb(x).List(LbItem)).Visible = False
            Else
                pf.PivotItems(lb(x).List(LbItem)).Visible = True
            End If
         Next LbItem
     Next pi
End If
Next x

Where am I going wrong?