CheckBox value hide/unhide pivot table items

[olympiac]

I am struggling to understand why the folllowing code doesn't update the pivot table based on a checkbox from another sheet. The first part of the code works perfectly but the second part doesn't update the pivot table. Can you assist, please?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim pt As PivotTable


If Sheets("MAIN").DrawingObjects("CheckBox58").Value = True Then

Set pt = Sheets("child2").PivotTables("PivotTableCHILD2")
pt.PivotFields("TeamName").PivotItems("item_name").Visible = True
Set pt = Nothing
End If
If Sheets("MAIN").DrawingObjects("CheckBox58").Value = False Then

Set pt = Sheets("child2").PivotTables("PivotTableCHILD2")
pt.PivotFields("TeamName").PivotItems("item_name").Visible = False
Set pt = Nothing
End If

 

[Jerry Sullivan]

Welcome Olympiac,


Answers to a few questions will help:

1. There are two kinds of Checkboxes. Is your checkbox a Form Control or an ActiveX control?
2. In which worksheet does your Worksheet_Change event appear (Main, Child2 or another sheet)?
3. Which version of Excel are you using?

 

[olympiac]

Hi JS411,

The checkbox is a Form Control and based on your inputs, I changed the code accordingly. The code below is fine now. Thanks

I have got 10 different checkboxes to tick or untick in order to update the pivot table. Do I need to repeat the code below for each of them or is it another way?

If Sheets("MAIN").CheckBoxes("CheckBox58").Value = xlOff Then

Set pt = Sheets("child2").PivotTables("PivotTableCHILD2")
pt.PivotFields("TeamName").PivotItems("item_name").Visible = False
Set pt = Nothing
End If

If Sheets("MAIN").CheckBoxes("CheckBox58").Value = xlOn Then

Set pt = Sheets("child2").PivotTables("PivotTableCHILD2")
pt.PivotFields("TeamName").PivotItems("item_name").Visible = True
Set pt = Nothing
End If
End Sub

 

[Jerry Sullivan]

Your code can be consolidated so that you don't need to repeat it 10 times.

Before going down that path though, is there any reason you aren't just using Filters on your Pivot Table?

 

[olympiac]

I am developing a dashboard with a main chart that can be updated based on the values chosen from comboxes and checkboxes. The spreadsheet contains - one sheet with the "Data"
- one sheet with two different pivot tables based on the "Data"
- one sheet with a table based on the results of the pivot tables (Getpivottable formula etc)
- one sheet with the dashboard with a Chart based on the table that is updates by the pivot tables. The checkboxes and comboxes will be used to update the pivot table and consequently the chart.

 

[Jerry Sullivan]

Okay, it sounds like you want functionality similar to Pivot Table filter, but instead of having the user's check items on the PivotTable dropdown, you prefer them to make those choices on a dashboard that also has a chart.

I can help with some code for that. Are all the checkboxes related to the same field in your Pivot Table ("TeamName")?

 

[Jerry Sullivan]

Assuming that you are only filtering the TeamName field, you could use something like this. Paste this code into Standard Module (not a Worksheet Module).

Edit the two array lists shown in blue font:
varCbxList = Your list of Check Box Names
varItemList = Your list of corresponding items to be filtered

Sub Use_Checkboxes_to_Filter_PivotTable()
    Dim PT As PivotTable
    Dim varCbxList() As Variant, varItemList() As Variant
    Dim i As Long
    Set PT = Sheets("child2").PivotTables("PivotTableCHILD2")
    On Error Resume Next
    varCbxList = Array("Check Box 1", "Check Box 2", "Check Box 3")
    varItemList = Array("Team A", "Team B", "Team C")
 
'----Ensure at least one item is checked and visible
    For i = LBound(varCbxList) To UBound(varCbxList)
        If Sheets("MAIN").CheckBoxes(varCbxList(i)).Value = xlOn Then
            PT.PivotFields("TeamName").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("MAIN").CheckBoxes(varCbxList(i)).Value = xlOff Then
            PT.PivotFields("TeamName").PivotItems(varItemList(i)).Visible = False
        Else
            PT.PivotFields("TeamName").PivotItems(varItemList(i)).Visible = True
        End If
    Next i
    Set PT = Nothing
End Sub

You could modify the code to build the lists by one of these methods:
1. Read all checkboxes on your sheet and have each checkbox linked to a cell that contains the corresponding team name.
2. Make a named range for each list.

These methods would allow you to add/remove/change checkboxes and teams without the need to go back and edit the VBA code.

Please let me know if this works for you.

 

[olympiac]

Your code works pefectly. I really appreciate your support. That's great.
With this piece of work completed, I can focus my effort on the code to manage the "Date" range. The requirement is to display the last 52 months or the data between two dates. Any inputs or links about that are welcome.
Thank you.

 

[Jerry Sullivan]

Great to hear that worked for you.

Regarding filtering your data for a date range, this link explains how to use Pivot Table filters to show only values that meet a certain criteria.
http://www.mrexcel.com/forum/showthread.php?t=518923

The example filters for values greater than or equal to 5, but you could specify a date range instead.

If you are wanting to be able to control from your dashboard instead of directly at the PivotTable, then the code could be a variation of the code I posted above.

It would go through each PivotTableItem and make Visible= True or False depending on whether or not it fell within the dates on your dashboard (or 52 Month default).

Hope this helps.

 

[olympiac]

I have already started to develop the dahboard with some inputs from the following link (http://pubs.<wbr>logicalexpressions.com/<wbr>Pub0009/LPMArticle.asp?ID=246) but
I will have a closer look at the link you sent above and let you know.
Cheers