PivotItem Run-time error: Unable to set Visible Property

[Graham Wiseman]

I have a pivot table built from a database which is
growing. I need to be able to control the Items shown in
the pivot for a specific field. For example: say my field
is called Salesman and I only want to see "Jim"
and "Fred". Here's the cruncher. As my database grows
there may be new items introduced (eg. Salesman "Harry").
Excel2002 automatically shows the new items in the pivot
table necxt time it's refreshed. But I don't want to
see "Harry"s data in the pivot table.

I have some VBA code which attempts to make ALL pivot
items for the "Salesman" field visible and then turn off
the ones I don't want.

However, most of the time (although not all of the time
and I don't understand why) I get "Run-time error '1004':
Unable to set the Visible property of the PivotItem class"

Can anyone tell me what I'm doing wrong please?

[Tom Ogilvy]

I believe you have to move the pagefield to be a rowfield, make your
changes, then move it back to being a pagefield.

Regards,
Tom Ogilvy

"Graham Wiseman" <gwis...@nortelnetworks.com> wrote in message
news:0ac901c33bf7$3af68060$a401...@phx.gbl...

[Debra Dalgleish]

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

Sub PivotShowItemAllField()
'For version 2000 -- show all items in specific field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Salesman")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
With pt.PivotFields("Salesman")
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 xlAscending, pf.SourceName
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Graham Wiseman]

Tom, thanks for your reply. However, I have no Page fields
in the pivot table in question. There is ONE Row Field and
One Column field and 5 Data fields. Here's the sub I'm
using to try to turn on all items in the field. The sheet,
pivot, pivot range and pivot field names are all global
variables (I'm using a data-driven method of controlling a
series of pivot tables - the criteria data is in another
hidden worksheet - that part works fine). The
HiddenItemsArray is populated by another sub which uses
the HiddenItems property to store the item values. I pass
the count of hidden items as a parameter.

I've noticed that if I MANUALLY set the field to "Show
All" before I run the sub it works fine. It's when some
items are hidden and some visible that I get the error.
Obviously the field I'm trying to make visible was hidden
otherwise it wouldn't be in the HiddenItemsArray. This is
a real teaser and I've been trying to solve this for days.
Regards
Graham Wiseman

Sub ShowHiddenItems(NoHiddenPvtItems)
Worksheets(SheetName).Activate
Worksheets(SheetName).Range
(PivotRangeName).Select 'select the pivot so we can start
manipulating it
Worksheets(SheetName).PivotTables
(PivotName).PivotFields(PivotFieldName).ShowAllItems = True
For ItemCount = 1 To NoHiddenPvtItems
Worksheets(SheetName).PivotTables
(PivotName).PivotFields(PivotFieldName).PivotItems
(HiddenItemsArray(ItemCount)).Visible = True
Next ItemCount
End Sub

>.
>

[Tom Ogilvy]

I stand corrected, see Debra's post.

Regards,
Tom Ogilvy

"Tom Ogilvy" <twog...@msn.com> wrote in message
news:e8lzNo$ODHA...@TK2MSFTNGP11.phx.gbl...

[Graham Wiseman]

WOW!! THANK YOU Debra!!

I did as you suggested and my code works like a charm.
I've been puzzling over this for days. I scoured Google
and MSN forums looking for the answer to this conundrum. I
should have spotted it myself - it was so obvious (he says
facetiously tee hee)

I was on the verge of abandoning what I was doing. You
saved my bacon! Thank you so much.
Best Wishes
Graham Wiseman