How to cycle through displayed PivotItems instead of the VisibleItemscollection

[Thomas V]

Hi,

I've run into a bit of a pickle with a VBA script.

The script sets formatting on PivotItems by cycling through the
VisibleItems collection. This works fine until I add a page field.
I've figured out that the Visible property of the PivotItem stays True
even though the item may be hidden by the selection on the page field.
The collection is therefore not that useful to me since it will cause
errors with any calls to get a range for any PivotItem hidden from
view by the page field. How can I get around this?

Below is an excerpt of the script in question:
==================================================
01 Dim pvtItems As PivotItems, pvtItem As PivotItem
02 Set pvtItems = pvtTable.PivotFields(myRow).VisibleItems
03 For Each pvtItem In pvtItems
04 pvtItem.LabelRange.Select
05
06 With Selection
07 .Borders.LineStyle = xlNone
08 .Borders(xlEdgeTop).LineStyle = xlContinuous
09 .Borders(xlEdgeTop).Weight = xlHairline
10 .Borders(xlEdgeTop).ColorIndex = xlAutomatic
11 .Borders(xlEdgeBottom).LineStyle = xlContinuous
12 .Borders(xlEdgeBottom).Weight = xlHairline
13 .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
14 End With
15 Next pvtItem
==================================================

The above script will fail on line 04 as soon as it hits an PivotItem
which is hidden from view by any other selections made in the pivot
table (e.g. on the page field).

Your help is greatly appreciated.

- Thomas

 

[Thomas V]

Hi,

I've run into a bit of a pickle with a VBA script.

The script sets formatting on PivotItems by cycling through the
VisibleItems collection. This works fine until I add a page field.
I've figured out that the Visible property of the PivotItem stays True
even though the item may be hidden by the selection on the page field.
The collection is therefore not that useful to me since it will cause
errors with any calls to get a range for any PivotItem hidden from
view by the page field. How can I get around this?

Below is an excerpt of the script in question:
==================================================
01      Dim pvtItems As PivotItems, pvtItem As PivotItem
02      Set pvtItems = pvtTable.PivotFields(myRow).VisibleItems
03      For Each pvtItem In pvtItems
04              pvtItem.LabelRange.Select
05
06              With Selection
07                      .Borders.LineStyle = xlNone
08                      .Borders(xlEdgeTop).LineStyle = xlContinuous
09                      .Borders(xlEdgeTop).Weight = xlHairline
10                      .Borders(xlEdgeTop).ColorIndex = xlAutomatic
11                      .Borders(xlEdgeBottom).LineStyle = xlContinuous
12                      .Borders(xlEdgeBottom).Weight = xlHairline
13                      .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
14              End With
15      Next pvtItem
==================================================

The above script will fail on line 04 as soon as it hits an PivotItem
which is hidden from view by any other selections made in the pivot
table (e.g. on the page field).

Your help is greatly appreciated.

- Thomas

Hi again,

I've found how to work around the issue. Since the routine causes
predictable errors one can simply trap the errors and work from there.

Here's an updated script:

01 Dim pvtItems As PivotItems, pvtItem As PivotItem
02 Set pvtItems = pvtTable.PivotFields(myRow).VisibleItems
03 For Each pvtItem In pvtItems
04 'Set error handling as we want it
05 Err.Clear
06 On Error Resume Next
07 pvtItem.LabelRange.Select 'this is the line we trap errors for
08 If Err.Number = 0 Then
09 With Selection
10 .Borders.LineStyle = xlNone
11 .Borders(xlEdgeTop).LineStyle = xlContinuous
12 .Borders(xlEdgeTop).Weight = xlHairline
13 .Borders(xlEdgeTop).ColorIndex = xlAutomatic
14 .Borders(xlEdgeBottom).LineStyle = xlContinuous
15 .Borders(xlEdgeBottom).Weight = xlHairline
16 .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
17 End With
18 End If
19 'Turn on regular error handling again
20 Err.Clear
21 On Error GoTo MyErrorHandler
22 Next pvtItem

I hope this is useful to others.

- Thomas

 

[minimaster]

I stumbled over a similar problem recently while trying to save
specific pivot table layouts. I solved it by using a case select and
making the pagefield temporarily a rowfield.
....
Dim pt as PivotTable
Dim objPF As PivotField
Dim pfItem As PivotItem

....
For Each objPF In pt.VisibleFields
Select Case objPF.Orientation
Case xlRowField
For Each pfItem In objPF.PivotItems
If pfItem.Visible = True Then
...
End If
Next pfItem
Case xlColumnField
For Each pfItem In objPF.PivotItems
If pfItem.Visible = True Then
...
End If
Next pfItem
Case xlPageField
objPF.Orientation = xlRowField
For Each pfItem In objPF.PivotItems
' Debug.Print pfItem.Parent.Name, pfItem.Name,
pfItem.Visible
If pfItem.Visible = True Then
....
End If
Next pfItem
objPF.Orientation = xlPageField
Case xlDataField

Case xlHidden

End Select
Next objPF