Validation List Used to Control Pivot Table

D

Des1gn1ng0ne

Board Regular
Joined
Jul 10, 2008
Messages
73
The theory:
Use one unlocked cell, that is limited to a validation list, to control which location (the first row item in the pivot table) is shown. This will allow the sheet to be locked, the location to be changed by any user, and no ability to tamper with the pivot table.

This is a modification of Greg Truby's code on: http://www.mrexcel.com/forum/showthread.php?t=278095

"pi.Visible = False" keeps coming up yellow. Any ideas or am I totally off?

Thanks for any and all help!
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable, pf As PivotField, pi As PivotItem
Dim B As String
B = Target.Cells(1, 1)
Set pt = Sheets("Location Total").PivotTables("Contact_Total")
Set pf = pt.PivotFields("BMD")
pt.PivotCache.Refresh
For Each pi In pf.PivotItems
    Select Case pi.Name
        Case "B"
              pi.Visible = True
        Case Else
              pi.Visible = False
    End Select
Next pi
Set pf = Nothing
Set pt = Nothing
End Sub

Brian S
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
I would try:
  • Turning on the macro recorder and manually toggling the visibilities just to make sure
    • protection is not getting in the way and
    • the syntax is okay
  • set a watch on pi (or turn on view locals) and/or pi.name and step through the code and observe what's going on

OH -- just thought of this, indeed try this first -- try running the FOR EACH PI loop one time initially and setting all visibilities to TRUE. Then go back and set the ones to false that need it. In most cases you cannot set all of the visibilities to FALSE, you must leave at least one TRUE. If your loop is trying to set the last visible item FALSE before it hits the item that will be TRUE, your pony throws a shoe.
 
Upvote 0
Greg, just wanted to thank you for the help! Here's the revision to the middle section that made it all work together.

Code: 
Dim pt As PivotTable, pf As PivotField, pi As PivotItem
Dim B As String
B = Range("B1")
Set pt = Sheets("Location Total").PivotTables("Contact_Total")
Set pf = pt.PivotFields("BMD")
pt.PivotCache.Refresh
For Each pi In pf.PivotItems  
        pi.Visible = True
Next pi
On Error Resume Next
For Each pi In pf.PivotItems
    Select Case pi.Name
        Case B
              pi.Visible = True
    Case Else
              pi.Visible = False
    End Select
Next pi
Set pf = Nothing
Set pt = Nothing
End Sub

Until the next time my imagination out runs my intelect...
Thank you again for the point in the right direction!

Brian S
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Macro to Filter Pivot Table
Replies
2
Views
529
howard
H
D
  • Question
VBA code to filter out blank values in a pivot table
Replies
2
Views
306
dragontbone
D
M
  • Question
Having trouble with latest date in vba code for a pivot table
Replies
1
Views
385
shina67
S
C
  • Question
Pivot Table VBA
Replies
0
Views
116
chaddres
C
M
  • Question
VBA Macro with option to filter Pivot Tables multiple times.
Replies
0
Views
568
Macro_nerd
M

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

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