Unable to set the Visible property of the PivotItem class

2
votes

Run-time error '1004': Unable to set the Visible property of the PivotItem class

Excel VBA: 

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pf = pt.PivotFields("Snapshot Date")

For Each pi In pf.PivotItems
If pi.Name <> "(blank)" Then
pi = DateValue(pi) 'I have tried using Cdate, Cdbl, Cstr as well.  
End If
Next pi

Errors happen here:

i = 1
Do Until i >= pf.PivotItems.count - 1

For Each pi In pf.PivotItems
pi.Visible = False 'Error here
Next pi

pf.PivotItems(i).Visible = True '.. And here!
pf.PivotItems(i + 1).Visible = True
Loop

The pivot items are dates, not sure if I need to format them before I can turn visibility on/off?

I have googled endlessly and could not find any solution to this :( Something about non-contiguous items, but I don't quite understand.

2
arraysexcelvbapivot-table
What version of Excel? Also, is it possible that you have unused items in your pivot table? This would cause the same error. See this Contextures page: http://www.contextures.com/xlpivot04.html#Option – Doug Glancy

2 Answers

2
votes

You have to have at least one visible PivotItem and you're setting them all to invisible. You need to loop through all the PivotItems, set the first one to visible = True, then test all the rest and set them accordingly, then test the first one and set it accordingly. That way you'll be sure there's always one visible PivotItem.

However, I'm not really sure what your test is. You have a loop, but don't increment "i", so it will loop forever always trying to set PivotItems 1 and 2 to visible.

I don't know why you're getting that second error, but if you can explain what you're trying to do there may be a better way.

0
votes

For anyone that finds this page using google (like I did), i found a way around this.

The problem appears to be only setting visible = true.

Setting visible = false seems to work fine.

If you disable the multiple selections and then re-enable it, all categories are then selected.

From that point you can then loop through and set visible = false to the ones you want.

See my example (working) code below :-

Dim table As PivotTable
Dim PvI As PivotItem

Set table = ActiveSheet.PivotTables("PivotTable3")
With table.PivotFields("ERROR CODE")
    .ClearAllFilters
    .EnableMultiplePageItems = False
    .CurrentPage = "(All)"
    .EnableMultiplePageItems = True
    For Each PvI In .PivotItems
        Select Case PvI.Name
        Case "Err0"
            PvI.Visible = False
        End Select
    Next
End With

Hope this helps someone.