Is an Excel bug that set PivotItem.Visible to true will generate 1004 Macro Error?

In a Excel PivotTable, I want to programically hide or show some PivotItems of a PivotField.

To hide, it works fine (just set mPivotField.PivotItems("xxx").visible = False).

However, to show, it does not work: mPivotField.PivotItems("xxx").visible = True. Program breaks with an error 1004, macro error.

When try to record a macro for setting a non-visible PivotItem to visible, macros recorded is to use ...PivotItem("xxx").visible = True. However, this generated macros never works as it generates 1004 error.

So, now my solution is let user manually set all pivot items to be visible first and click a button which will programically hide all un-want pivot items.

It is not perfect.

Furthermore, it is very slow and not acceptable: a pivot table of 40,000 records with 600 distinct SKU all visible. A program loops all these 600 pivot items and set 599 of them invisble while keeping one of them still visible, it takes 5 mintues (I have tried, set Application.Calculation to manual and even hide the Excel to prevent screen updating, it is still that slow). However, I use the Excel UI to do this (starting from 600 pivot items are all visible, I dis-select 599 of them from visible and leaving one as visible), it takes 3 seconds to response.

I have researched this topic for 2 days without good answer:
"Programically hide and show pivot items in Excel 2003"

I hope somebody can help.

Answer this question

Is an Excel bug that set PivotItem.Visible to true will generate 1004 Macro Error?

gurpreet11

The engineer found some additional information on this issue:

About Error:"Unable to Set Visible Property PivotItem class"< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

SYMPTOMS

When you run a recorded Visual Basic, Applications Edition, macro to set up a pivot table in which discontiguous items are hidden in the PivotTable Field dialog box, you may receive the following error message:

Unable to set the Visible property of the PivotItem class

CAUSE

Only contiguous items in a PivotTable Field can be hidden.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version < xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />5.0c.

WORKAROUND

To work around this problem, remove or disable (use an apostrophe to comment out) the line or lines that set the visible property of discontiguous items in the field to False; removing or disabling this line will allow your macro to run successfully.

For example, in the following code, you would comment out the fourth line down to disable the code that causes the error:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("field1")

   .PivotItems("1").Visible = False

   .PivotItems("2").Visible = False '

   .PivotItems("7").Visible = False

End With
Soe
I find that the work-a-round solution does not work.

If an invisible Pivot Item is before a visible Pivot Item, there is no work-a-round way to make it visible!!!
bbossi
Happy to know the condition causing the problem and work-a-round solution
Mif Wright
I have a similar problem. I'm working with a Pivot based on an OLAP Cube. I have tried the above mentioned solution but it does not work. I tried to somehow use "HiddenItemsList" property as well but to no avail. I just need to be able to make one Pivot Item visible at a time and keep all the others hidden and then do some basic charting.

Any help will be much appreciated.
Dion

PatPhilippot

The data table

Item	YYMM	SaleQty
ITEM-A	501	232
ITEM-A	502	231
ITEM-A	503	21
ITEM-A	504	232
ITEM-A	505	12
ITEM-B	501	232
ITEM-B	502	231
ITEM-B	503	21
ITEM-B	504	232
ITEM-B	505	12
ITEM-C	501	232
ITEM-C	502	231
ITEM-C	503	21
ITEM-C	504	232
ITEM-C	505	12

The Pivot Table

Sum of SaleQty	YYMM
Item	501	502	503	504	505	Grand Total
ITEM-A	232	231	21	232	12	728
ITEM-B	232	231	21	232	12	728
ITEM-C	232	231	21	232	12	728
Grand Total	696	693	63	696	36	2184

The Macro

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/20/2006 by SWISSAM LTD
With ActiveSheet.PivotTables("PivotTable1").PivotFields("YYMM")
.PivotItems("503").Visible = False
End With
End Sub
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/20/2006 by SWISSAM LTD
'
With ActiveSheet.PivotTables("PivotTable1").PivotFields("YYMM")
.PivotItems("503").Visible = True
End With
End Sub

The macro will not break if PivotFields("YYMM") is not sorted.

The macro will break if PivotFields("YYMM") is sorted.

To sort PivotFields("YYMM"): (1) Right click "YYMM" in the pivot table (2) Field Setting (3) Advance (4) AutoSortOption: Ascending.

To un-sort PivotFields("YYMM"): (1) Right click "YYMM" in the pivot table (2) Field Setting (3) Advance (4) AutoSortOption: Manual

My suggested work-around is to un-sort the pivot field before setting visible and then sort the pivot field again instead of your suggestion of remarking the statement of setting the pivotItem visible which cause the error. It not an acceptable as a work-around. It seems to me that asking an expert that what can I work-around if I do this which will have an error and the expert suggest that don't this and it will not have error. I can accept this answer is a smart answer in some case, but not this time.

By the way I like Microsoft products, it is very good!

johnson hk

9072

HI brenda,

You may be right ...

but if you have more than 1 row field your code will give the error(Unable to set the Orientation property of the PivotField class )...i am writting the code in vb.net and getting the error...my code is

objXcel.ActiveSheet.PivotTables("PivotTable1").PivotFields("Task Name").Orientation = XlPivotFieldOrientation.xlRowField

objXcel.ActiveSheet.PivotTables("PivotTable1").PivotFields("Task Name").Position = 1

'Here the problem

objXcel.ActiveSheet.PivotTables("PivotTable1").PivotFields("Data").Orientation = XlPivotFieldOrientation.xlRowField 'xl.XlPivotFieldOrientation.xlRowField

objXcel.ActiveSheet.PivotTable("PivotTable1").PivotFields("Data").Position = 2

objXcel.ActiveSheet.PivotTables("PivotTable1").PivotFields("Level 1 Task").Orientation = XlPivotFieldOrientation.xlPageField

Regards

Mayab
wetheral

Hi,

Here's more from the support engineer...

Thanks for your following up on this issue. But according to my testing, the code can work fine in every condition. I have tested all the conditions of PivotFields(“FieldName”).Orientation, and there ware no errors and work fine when setting PivotItems(“2005-07”).Visible=True< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Here is my testing code:

=====

Sub Macro1()

   ActiveSheet.PivotTables("PivotTable4").PivotFields("Time").Orientation = xlColumnField

   ‘ActiveSheet.PivotTables("PivotTable4").PivotFields("Time").Orientation = xlRowField

   ‘ActiveSheet.PivotTables("PivotTable4").PivotFields("Time").Orientation = xlPageField



   With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time")

        .PivotItems("2005-7").Visible = True

    End With

End Sub

=======

The difference between the 3 orientation values is , that field will be shown in different layout. But it can be set Visible to true in every condition. So, maybe you can try it again following my code. If you still meet problems, would you please show me the script to reproduce your error and paste me the sceenshoots of the error message

I hope this helps!

-brenda (ISV Buddy Team)
kdub98
In suggested workaround in About Error:"Unable to Set Visible Property PivotItem class" does not work.

To workaround: Set the pivot field sorting to xlManual before setting its pivot item visible.
Stephen Davies.
Hi, There

I have the nasty Visible property problem. With the following code i get 1004 error in line (If PivItem.Visible = False Then PivItem.Visible = True). AutoSort does'nt help me at all. i'am using Excell 2003 SP2

Please help, Jari

Sub updateOpenBeDelayedPivot()
' Filtteroi SummarySheetin kuukausiluvut
Dim currentyear As Integer
currentyear = (DatePart("yyyy", Date))

Dim currentmonth As String ' 1,11 -> 01,11
Dim mLen As Integer
currentmonth = (DatePart("m", Date))
mLen = Len((DatePart("m", Date)))
If mLen = 1 Then
currentmonth = 0 & currentmonth
End If

filterOpenBeDelayedData "OpenBeDelayed", currentmonth, currentyear

End Sub

Sub filterOpenBeDelayedData(pivotname As String, currentmonth As String, currentyear As Integer)

Dim PivItem As PivotItem
Sheets("OpenBeDelayed").Select

' Not in use - Varmistetaan etta kaikki itemit on nakyvissa

' For Each PivItem In _
'ActiveSheet.PivotTables(pivotname).PivotFields("DeliveryDay").PivotItems
' If PivItem.Visible = False Then
' PivItem.Visible = True
' End If
'Next PivItem
Set pt = ActiveSheet.PivotTables(pivotname)
Set pf = pt.PivotFields("DeliveryDay")
With pt.PivotFields("DeliveryDay")
pf.AutoSort xlManual, pf.SourceName

For Each PivItem In _
ActiveSheet.PivotTables(pivotname).PivotFields("DeliveryDay").PivotItems

If CDate(PivItem.Name) < Date Then
If PivItem.Visible = False Then
PivItem.Visible = True ' <<<<<<<<<<<<<<<<<<<<<<<<******************error 1004 point
End If
End If
If CDate(PivItem.Name) >= Date Then
PivItem.Visible = False
End If

Next PivItem

pf.AutoSort xlAscending, pf.SourceName
End With

End Sub
NewASPNETUser
Hi,

Here's the latest from the support engineer:

No matter if a Pivot Item is invisible or visible, we can set .Visible = False/true.

I also try to change the PivotFields("Time").Orientation, but according to my testing, the code can work fine in every condition.

So if possible please ask customer give me his demo code, and I can reproduce the error exactly.

Please feel free to post your code sample or email it to budsup@microsoft.com and I'll get to the engineer.

-brenda (ISV Buddy Team)
mat_net

Per the support engineer:

The following is my test codes without any Error, please let customer try it. Any question, feel free to let me knowJ. < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

==============================================

Sub Macro2()

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time")
        .PivotItems("2005-7").Visible = True
    End With
End Sub

Sub Macro3()

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time")
        .PivotItems("2005-7").Visible = False
    End With
End Sub

==============================================

-brenda (ISV Buddy Team)
dodgeshadow
Hi Brenda,

The condition of making following macro2 break is that: Try to change the PivotFields("Time").Orientation to either xlRowField or xlColumnField before running the macro. If the PivotFields("Time").Orientation is xlPageField, the following macro2 works fine.

Sub Macro2()

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time")
        .PivotItems("2005-7").Visible = True
    End With

End Sub
BobH
Per the support engineer:

With the customer code, I can reproduce the problem eventually in my environment.

It is caused by the sort action that we never mentioned it before.And it also has been confirmed to be a bug of excel 2003 and Excel XP.

And it will be won’t fix as a result till now.

To prevent the error, we should 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

===

-brenda (ISV Buddy Team)
Is an Excel bug that set PivotItem.Visible to true will generate 1004 Macro Error?

Answer this question