CABRLU63
asked on
Excel 2007 Pivot Chart VBA - Charts Check Boxes
Hello,
I had created a Pivot Table
9 columns
Row Labels with system names
then 8 columns with Averages and Counts
Then I've created Chart
I want to add Check Buttons on chart so that I can uncheck or check and that way manipulate visibility of the 8 comlumns on the chart. Bar chart.
My code that doesnt work well:
Sub Use_Checkboxes_to_Filter_P ivotTable1 ()
Dim PT As PivotTable
Dim varCbxList() As Variant, varItemList() As Variant
Dim i As Long
Set PT = Sheets("ResponseTime-Suppo rt Grp").PivotTables("PivotTa ble1")
On Error Resume Next
varCbxList = Array("Check Box 5", "Check Box 6", "Check Box 7", "Check Box 8", "Check Box 9", "Check Box 10", "Check Box 11", "Check Box 12")
varItemList = Array("Count of Resolved", "Average of QueueTime", "Average of ResWaitTime", "Average of WorkTIme", "Average of RepairTime", "Average of VerifyTime", "Count of Closed", "Average of ServiceTime")
'--Ensure at least one item is checked and visible
For i = LBound(varCbxList) To UBound(varCbxList)
If Sheets("ResponseTime-Suppo rt Grp").CheckBoxes(varCbxLis t(i)).Valu e = xlOn Then
PT.PivotFields("Count of Resolved").PivotItems(varI temList(i) ).Visible = True
Exit For
Else
If i = UBound(varCbxList) Then
MsgBox "You must have at least one item checked"
Exit Sub
End If
End If
Next i
For i = LBound(varCbxList) To UBound(varCbxList)
If Sheets("ResponseTime-Suppo rt Grp").CheckBoxes(varCbxLis t(i)).Valu e = xlOff Then
PT.PivotFields("Count of Resolved").PivotItems(varI temList(i) ).Visible = False
Else
PT.PivotFields("Count of Resolved").PivotItems(varI temList(i) ).Visible = True
End If
Next i
Set PT = Nothing
End Sub
Thanks!
I had created a Pivot Table
9 columns
Row Labels with system names
then 8 columns with Averages and Counts
Then I've created Chart
I want to add Check Buttons on chart so that I can uncheck or check and that way manipulate visibility of the 8 comlumns on the chart. Bar chart.
My code that doesnt work well:
Sub Use_Checkboxes_to_Filter_P
Dim PT As PivotTable
Dim varCbxList() As Variant, varItemList() As Variant
Dim i As Long
Set PT = Sheets("ResponseTime-Suppo
On Error Resume Next
varCbxList = Array("Check Box 5", "Check Box 6", "Check Box 7", "Check Box 8", "Check Box 9", "Check Box 10", "Check Box 11", "Check Box 12")
varItemList = Array("Count of Resolved", "Average of QueueTime", "Average of ResWaitTime", "Average of WorkTIme", "Average of RepairTime", "Average of VerifyTime", "Count of Closed", "Average of ServiceTime")
'--Ensure at least one item is checked and visible
For i = LBound(varCbxList) To UBound(varCbxList)
If Sheets("ResponseTime-Suppo
PT.PivotFields("Count of Resolved").PivotItems(varI
Exit For
Else
If i = UBound(varCbxList) Then
MsgBox "You must have at least one item checked"
Exit Sub
End If
End If
Next i
For i = LBound(varCbxList) To UBound(varCbxList)
If Sheets("ResponseTime-Suppo
PT.PivotFields("Count of Resolved").PivotItems(varI
Else
PT.PivotFields("Count of Resolved").PivotItems(varI
End If
Next i
Set PT = Nothing
End Sub
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.