How can I set PivotItem.Visible property to True?

Y

yumi

What's wrong with the following codes? I can set visible=false but when I try to set it to true run I receive run time error
at "pi.visible = True"

Dim pi as pivotitem
sheets("pivot").select
For Each pi in ActiveSheet.PivotTables("Pivottable1").PivotFields("fieldname").PivotItems
If pi.Visible = False Then
pi.Visible = True
End If
Next
 
Y

yumi

(correction!)Please ignore 'run' after 'set it to true'....
Any help would be appreciated!
 
K

kanan

HI
Iam not sure how this issue was resolved. Because i have a similar problem in my worksheet with a pivottable
Pls explain what you did
thank
Kanan
 
T

Tom Ogilvy

Debra Dalgleish says that you have to set the sort option to manual as in
this code;


From: Debra Dalgleish ([email protected])
Subject: Re: PivotItem Run-time error: Unable to set Visible Property
Newsgroups: microsoft.public.excel.programming
Date: 2003-06-26 08:58:23 PST


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


--
Regards,
Tom Ogilvy
kanan said:
HI,
Iam not sure how this issue was resolved. Because i have a similar problem
in my worksheet with a pivottable.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top