2

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Ramify
  • 39
  • 2
  • 7
  • 1
    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 May 06 '12 at 19:25

2 Answers2

2

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.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • It gives you an error because PivotTable sees all the fields, not the ones displayed. – Setrino Jul 10 '15 at 14:53
  • Is this a comment for me? I don't really understand what you're saying. I'm not getting any errors - it's the OP that's getting the errors. – Dick Kusleika Jul 10 '15 at 18:04
  • Well I explained to you the part "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." Basically everything is visible for PivotTable during creation of it. So you have to loop through and hide immediately all the fields that you don't need before adding only the fields that you require. I did it like this https://jsfiddle.net/do3hbc6c/ – Setrino Jul 13 '15 at 15:11
0

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.