dynamically filtered pivot table

[mrsckum]

I'm trying to make a pivot table that will dynamically hide a section o
its contents based on a boolean operator the user can set. Becaus
this boolean is used in several places, I don't want to require th
user to manually set the visibility parameters for the pivot table.

It seems like the only way to do this effectively is to have th
booleans set by a button, and have the button not only toggle th
boolean, but also change the visibility in the pivot table.

here's the code i've tried:

Sub ToggleButton1_Click()


If ToggleButton1.Caption = "Include" Then 'Check caption, the
change it.
ToggleButton1.Caption = "Exclude"
ToggleButton1.BackColor = 4966415

Else
ToggleButton1.Caption = "Include"
ToggleButton1.BackColor = 16776960



End If

With ActiveSheet.PivotTables("PivotTable1").PivotFields("MODEL2")
'.PivotItems("20_40_60").Visible = bTFE20_40_60
.PivotItems("2").Visible = bTFE2
.PivotItems("3").Visible = bTFE3
.PivotItems("4").Visible = bTFE4
.PivotItems("5").Visible = bTFE5
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub



Unfortunately, it tells me "Unable to set the Visible property of th
PivotItem class"

That part of the function I took right out of the macro editor (
created a macro to find out the correct function calls, the contents o
the macro work fine until i copy them into this function). I realiz
it's messy to update all the booleans every time, but i just want t
get SOMETHING to work.

I don't know what to do!! help!!

Andr

 

[mrsckum]

I think the problem has to do with setting the visibility of th
pivotitem class from within a button

 

[Debra Dalgleish]

To prevent the error, set the Sort for the field to Manual. You can do
this in the code, for example:

'==================================
Sub PivotShowItemResetSort()
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
'returns sort order to previous setting
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim intASO As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.VisibleFields
intASO = pf.AutoSortOrder
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
If pi.Visible <> True Then
pi.Visible = True
End If
Next pi
pf.AutoSort intASO, pf.SourceName
Next pf
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'================================

 

[mrsckum]

Well, the table already is set to sort manually. I've been scourin
around the net today, and it looks like it has something to do with th
scope of the function.

Let me simplify the problem even further...


Public Sub ToggleButton_TFE2_Click()
'for the TFE -2 this will toggle the named field "bTFE2"

ToggleButton_TFE2.Caption = ToggleButton_TFE2.Value


ActiveSheet.PivotTables("PivotTable1").PivotFields("MODEL2").PivotItems("TFE2").Visibl
= Range("bTFE2").Value

End Sub


That SHOULD change the visibility of the corresponding TFE2 pivot ite
in the pivot table to match the value in the range. It keeps tellin
me that it's unable to set the visible property of the pivotitem class
which is bunk!

What's really strange is that if i allow it to break when it fails
manually typing in TRUE or FALSE in the linked cell (and therefor
manually changing the value of bTFE2) DOES change the visibility in th
pivot table!!!

I'm flummoxed!

Andr