• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

Excel 2007 Pivot Chart VBA - Charts Check Boxes


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_PivotTable1()

Dim PT As PivotTable
Dim varCbxList() As Variant, varItemList() As Variant
Dim i As Long
Set PT = Sheets("ResponseTime-Support Grp").PivotTables("PivotTable1")
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-Support Grp").CheckBoxes(varCbxList(i)).Value = xlOn Then
            PT.PivotFields("Count of Resolved").PivotItems(varItemList(i)).Visible = True
            Exit For
            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-Support Grp").CheckBoxes(varCbxList(i)).Value = xlOff Then
            PT.PivotFields("Count of Resolved").PivotItems(varItemList(i)).Visible = False
            PT.PivotFields("Count of Resolved").PivotItems(varItemList(i)).Visible = True
        End If
    Next i
    Set PT = Nothing
End Sub

1 Solution

xlOff (-4146) is not an accepted value for CheckBox.Value

it should 0 (No), 1 (Yes) or 2 (Greyed)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now