Excel PivotField Orientation is wrong?

Question

I'm using Excel 2010 and writing a VBA function to automatically format various metrics in a pivot table as it is changed by the user (so that formatting is preserved when they add/remove fields). I'm looping through the PivotFields collection to do this. In order to change the format the PivotField needs to be visible in the pivot table, otherwise it throws an error. So I figured I could just check the Orientation property of the PivotField and only handle those set to an Orientation of xlDataField.

However, when I run the function, all data fields are showing an Orientation of xlHidden, even if they are visible in the PivotTable.

I'm just doing a simple function like this

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim pt As PivotField

    For Each pt In ActiveSheet.PivotTables("PivotTable1").PivotFields

        If VBA.InStr(1, pt.Caption, "COMP", vbTextCompare) And pt.Orientation = xlDataField Then
            pt.Function = xlAverage
            pt.NumberFormat = "0.00%"
        End If

    Next pt

End Sub

I've even checking all PivotFields by using the following:

Debug.Print pt.Caption & " " & pt.Orientation

Anything not in the Page, Row or Column fields shows as Hidden. Nothing appears as a Data field. Am I missing something? It doesn't matter what I have set as Data fields, nothing is tagged as xlDataField. Should I try doing this another way? I've tried using the PivotFields.Hidden property but I get errors trying to access it.

Kazimierz Jawor Kazimierz Jawor · Accepted Answer · 2013-03-22T18:59:59

Instead of looping through PivotFields you should loop through DataFields which are those in xlDataField orientation.

If you need to check if there is any before loop just test:

If ActiveSheet.PivotTables(1).DataFields.Count > 0 Then

That return 0 if there is nothing in data orientation.

EDITED: one more thing- don't forget to switch off events as your procedure results with PT changes which fire that event again. So, Application.EnableEvents = False at the beginning and =True in the end.

Excel PivotField Orientation is wrong?

1 Answers