CheckBox value hide/unhide pivot table items

O

olympiac

Board Regular
Joined
Sep 26, 2010
Messages
158
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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")?
 
Upvote 0
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

Rich (BB code): 
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA code to filter out blank values in a pivot table
Replies
2
Views
497
dragontbone
D
M
  • Solved
Loop through unfiltered Pivot Table items
Replies
1
Views
288
mikeymay
M
H
  • Solved
Macro to Filter Pivot Table
Replies
2
Views
675
howard
H
M
  • Question Question
VBA Macro with option to filter Pivot Tables multiple times.
Replies
0
Views
653
Macro_nerd
M
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
166
Anthony47
A

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top