Excel VBA Unable to set the Visible property of the PivotItem class

By Minh • Aug 7th, 2010 • Category: Office

In versions of Microsoft Excel before 2007, chances are AutoSort is enabled if you are getting the error message Unable to set the Visible property of the PivotItem class or Method ‘Visible’ of object ‘PivotItem’ failed. Microsoft has a bulletin, KB114822, for it. Basically only contiguous PivotItems in a PivotField can be hidden or unhidden. This appears to be fixed in Microsoft Excel 2007 and later.

ManualUpdate and AutoSort Workaround:

Dim mySheet As Worksheet
Dim myTable As PivotTable, myField As PivotField, myItem As PivotItem

Set mySheet = Sheets(1)
Set myTable = mySheet.PivotTables(1)
Set myField = myTable.PivotFields(1)

For Each myItem In myField.PivotItems
    myField.AutoSort xlManual, myField.SourceName
    myTable.ManualUpdate = True

    myItem.Visible = True

    myTable.ManualUpdate = False
    myField.AutoSort xlAscending, myField.SourceName
Next myItem

The workaround is to temporarily enable ManualUpdate in the PivotTable and disable AutoSort in the PivotField. Download pivot.xls for a sample of the error and the workaround.

Minh is a technology junkie.
Email this author | All posts by Minh

4 Responses »

Rohit Ghiya on January 26th, 2011 at 10:51 am:
I am getting error when it reads “myField.AutoSort xlManual, myField.SourceName”
Error : Application defined or object defined error
Zoltán Makári on February 13th, 2012 at 4:43 am:
I’ve tried your pivot.xls and it worked fine (Office 2010). Then I copied the code to my macro and it’s not working anymore… I am confused
that guy on July 27th, 2012 at 11:36 am:
Pretty! This has been an incredibly wonderful article.
Thank you for providing these details.
Jen on December 3rd, 2012 at 9:47 am:
Hi
I am having a problem where I cannot get my code to make all the pivotitems in the pivot table to display. I found this code while looking through the internet, but I still get the same issue. When I get to the 3rd pivotitem in the pivot table it gives me the following error: Unable to set the Visible property in the PivotItem class”. I get the same error for the above code, a Do while loop and with For Next. I am using Excel 2007. Has anyone got any ideas?
Thanks
Jen

MinhTech.com