Checkboxes in VBA to update Pivot Table

[Cokeistan]

I'm not sure why the following code doesn't update my pivot table based on a checkbox selection. I can get the pivot table to change when I click one checkbox, but when I click an additional checkbox does not work. I am developing a dashboard for a project in which I do not want to use slicers as a filter (awkward I know). Any help anyone could give me would be much appreciated.


Here's the code:

[pre]

Sub Use_Checkboxes_to_Filter_PivotTable()
Dim PT As PivotTable
Dim varCbxList() As Variant, varItemList() As Variant
Dim i As Long
Set PT = Sheets("TeamMember").PivotTables("PivotTable2")
On Error Resume Next
varCbxList = Array("Check Box 1", "Check Box 2", "Check Box 3")
varItemList = Array("October", "September", "August")

For i = LBound(varCbxList) To UBound(varCbxList)
If Sheets("TeamMember").CheckBoxes(varCbxList(i)).Value = xlOn Then
PT.PivotFields("Months").PivotItems(varItemList(i)).Visible = True
Exit For
Else
If i = UBound(varCbxList) Then
MsgBox "You much have at least one item checked"
Exit Sub
End If
End If
Next i
For i = LBound(varCbxList) To UBound(varCbxList)
If Sheets("TeamMember").CheckBoxes(varCbxList(i)).Value = xlOff Then
PT.PivotFields("Months").PivotItems(varItemList(i)).Visible = False
Else
PT.PivotFields("Months").PivotItems(varItemList(i)).Visible = True
End If
Next i
Set PT = Nothing
End Sub

[/pre]

 

[Hui]

Cokeistan


Firstly, Welcome to the Chandoo.org Forums


When posting code please put a single ` (next to the 1, below Esc key) before and after the code as, it maintains the indents and makes it so much more readable


Is it possible for you to post the file ?

Refer the sticky green posts for details

 

[Cokeistan]

The problem has been fixed, thanks for the info