MinhTech.com

Yet another technology tutorial blog.

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

By • 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.

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

5 Responses »

  1. I am getting error when it reads “myField.AutoSort xlManual, myField.SourceName”

    Error : Application defined or object defined error

  2. 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 🙁

  3. Pretty! This has been an incredibly wonderful article.

    Thank you for providing these details.

  4. 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

  5. For what it’s worth, this solution worked for me. I actually believe I have excel 2010, so not sure that the issue has actually been resolved with later versions of excel as mentioned in this blog. Thanks for the code, it saved me a tremendous headache.

Leave a Reply