Solved

Excel 2007 Pivot Chart VBA - Charts Check Boxes

Posted on 2013-11-25
1
565 Views
Last Modified: 2013-12-03
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_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
        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-Support Grp").CheckBoxes(varCbxList(i)).Value = xlOff Then
            PT.PivotFields("Count of Resolved").PivotItems(varItemList(i)).Visible = False
        Else
            PT.PivotFields("Count of Resolved").PivotItems(varItemList(i)).Visible = True
        End If
    Next i
    Set PT = Nothing
End Sub


Thanks!
0
Comment
Question by:CABRLU63
1 Comment
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39677267
Hi,

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

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

Regards
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question