PivotTable Number Formatting with VBA

[Rob71179]

Hi,

I am working in Excel 2007, and I have a pivot table with several fields that I want to make data fields, but not at the same time. Essentially, I want to be able to toggle between Revenue, Quantity, and ASP (which is a formula). The problem is that every time I do a number format for Revenue, for example, de-select it as a data field (i.e. Sum of Revenue), and then re-select it I lose my number formatting. To fix this, I am trying to write a VBA macro that will refresh the formatting depending on which field is selected (i.e. Revenue, Quantity, or ASP). Revenue will have the format "$#,##0", Quantity - "#,##0", and ASP "$#,##0.00".

I have written some simple code as a test.

Sub ReTest()
If ActiveSheet.PivotTables("Rev").PivotFields("Revenue").Orientation = DataFields Then
Range("F27").Select
ActiveCell.Formula = "hello"
Range("F28").Select

End If
End Sub

Essentially, I want to write an If-statement that will determine if Revenues are in the data fields, and if so to write the word hello in a cell outside the pivot table. However, when I execute this code, "hello" appears in the specified cell when Revenue, Qunatity, or even no data field is selected leading me to believe that my If-statement is not being read properly. Can you please help?

Thank you!

 

[Jerry Sullivan]

Welcome to the Board,

Change:
.Orientation= DataFields to
.Orientation= xlDataField

 

[Rob71179]

Thank you, Jerry, but unfortunately I am still experiencing the same problem. The Revenue and Quantity fields are check boxes in the PivotTable Field List. "hello" is printed in the selected cell when

1) The Revenue box is the only field checked and placed in the Sum of Values area.
2) The Quantity box is the only field checked and placed in the Sum of Values area.
3) Absolutely NO box is checked in the PivotTable Field List.

 

[Jerry Sullivan]

The image below might help illustrate what is happening.

1) The Revenue box is the only field checked and placed in the Sum of Values area.

For this scenario, your PF Orientation will be:

Excel Workbook
	F	G	H
29	Collection	Name	Orientation
30	in PivotFields:	Store	1 : xlRowField
31	in PivotFields:	Revenue	0 : xlHidden
32	in PivotFields:	Quantity	0 : xlHidden
33	in PivotFields:	ASP	0 : xlHidden
34	in DataFields:	Sum of Revenue	4 : xlDataField
Sheet1

So the problem with your If statement is that you are testing for:
PivotFields("Revenue").Orientation = xlDataField

When in fact you want to test that all these statements are true
PivotFields("Sum of Revenue").Orientation = xlDataField
PivotFields("Revenue").Orientation = xlHidden
PivotFields("Quantity").Orientation = xlHidden
PivotFields("ASP").Orientation = xlHidden

Using this information, try reworking your "hello" code, and I'll be glad to help if you get stuck.

This code could help you by generating a table of current Orientation Values, like the one shown above, as you move your PivotFields around.

Sub ShowCurrentOrientations()
    Dim lRow As Long: lRow = 29 'Starting Row for info output
    Dim pf As PivotField
    Dim vONames As Variant
    vONames = Split("xlHidden xlRowField xlColumnField xlPageField xlDataField")
    Range("F1:H1").Offset(lRow - 1) = Array("Collection", "Name", "Orientation")
    
    With ActiveSheet.PivotTables("Rev")
        For Each pf In .PivotFields
            lRow = lRow + 1
            Cells(lRow, "F") = "in PivotFields:"
            Cells(lRow, "G") = pf.Name
            Cells(lRow, "H") = pf.Orientation & _
                " : " & vONames(pf.Orientation)
        Next pf
        For Each pf In .DataFields
            lRow = lRow + 1
            Cells(lRow, "F") = "in DataFields:"
            Cells(lRow, "G") = pf.Name
            Cells(lRow, "H") = pf.Orientation & _
                " : " & vONames(pf.Orientation)
        Next pf
    End With
End Sub

 

[Rob71179]

Hi Jerry,

Your suggestion works. The code I used is as follows:

Sub ReTest()
If ActiveSheet.PivotTables("Rev").PivotFields("Sum of Revenue").Orientation = xlDataField Then
Range("F27").Select
ActiveCell.Formula = "hello"
Range("F28").Select

End If
End Sub

However, the code only works if the check box titled "Revenue" is selected. If, for example, "Revenue" is de-selected and "Quantity" is selected, then I get the following error.

'Run-time error 1004':
Unable to get the PivotFields property of the PivotTable class

The program identified the error in the IF statement. I'm thinking that this is because the program is looking for the DataField "Sum of Revenue", and cannot locate it because the "Sum of Quantity" is in the DataFields bc the "Quantity" box is checked.

Essentially, I want the program to look for the "Sum of Revenue" and if it finds it, then it prints "hello". If the program does not find "Sum of Revenue" then nothing happens.

Thank you for your help, Jerry!

 

[Jerry Sullivan]

Rob,

The example below shows how you can set the Excel's VBA error handler to
keep going (Resume Next) if it hits an error.
That will allow you to test whether the PivotField you name actually exists.

Sub ReTest2()
    Dim pf As PivotField
    On Error Resume Next
    With ActiveSheet
        Set pf = .PivotTables("Rev").PivotFields("Sum of Revenue")
        If Not pf Is Nothing Then
            .Range("F27") = "hello"
        Else
            .Range("F27") = "goodbye"
        End If
    End With
    On Error GoTo 0
End Sub

 

[Rob71179]

Hi Jerry,

It worked like a charm! Thanks so much for your help!

Rob

 

[Jerry Sullivan]

I'm happy to hear that Rob. You're welcome.

 

[Rob71179]

Hi Jerry,

I completed the project I was working on, and the final code is below if you're interested. Thanks again!

Sub PivotNumberFormat()
On Error GoTo ErrorHandler
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Quantity").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of Quantity")
.NumberFormat = "#,##0"
End With
End If
On Error GoTo ErrorHandler2
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ASP").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of ASP")
.NumberFormat = "$#,##0.00"
End With
End If
On Error GoTo ErrorHandler3
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Revenue").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of Revenue")
.NumberFormat = "$#,##0"
End With
End If
On Error GoTo ErrorHandler4
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ASP per Area/Vol").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of ASP per Area/Vol")
.NumberFormat = "$#,##0.0000"
End With
End If
On Error GoTo ErrorHandler5
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Total Area/Volume").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of Total Area/Volume")
.NumberFormat = "#,##0"
End With
End If
Exit Sub

ASP:
On Error GoTo ErrorHandler2
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ASP").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of ASP")
.NumberFormat = "$#,##0.00"
End With
End If
On Error GoTo ErrorHandler3
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Revenue").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of Revenue")
.NumberFormat = "$#,##0"
End With
End If
On Error GoTo ErrorHandler4
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ASP per Area/Vol").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of ASP per Area/Vol")
.NumberFormat = "$#,##0.0000"
End With
End If
On Error GoTo ErrorHandler5
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Total Area/Volume").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of Total Area/Volume")
.NumberFormat = "#,##0"
End With
End If
Exit Sub

Revenue:
On Error GoTo ErrorHandler3
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Revenue").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of Revenue")
.NumberFormat = "$#,##0"
End With
End If
On Error GoTo ErrorHandler4
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ASP per Area/Vol").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of ASP per Area/Vol")
.NumberFormat = "$#,##0.0000"
End With
End If
On Error GoTo ErrorHandler5
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Total Area/Volume").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of Total Area/Volume")
.NumberFormat = "#,##0"
End With
End If
Exit Sub
ASP_Area_Vol:
On Error GoTo ErrorHandler4
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ASP per Area/Vol").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of ASP per Area/Vol")
.NumberFormat = "$#,##0.0000"
End With
End If
On Error GoTo ErrorHandler5
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Total Area/Volume").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of Total Area/Volume")
.NumberFormat = "#,##0"
End With
End If
Exit Sub

Total_Area_Vol:
On Error GoTo ErrorHandler5
If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Total Area/Volume").Orientation = xlDataField Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields.Item("Sum of Total Area/Volume")
.NumberFormat = "#,##0"
End With
End If
Exit Sub

ErrorHandler:
Resume ASP

ErrorHandler2:
Resume Revenue
ErrorHandler3:
Resume ASP_Area_Vol

ErrorHandler4:
Resume Total_Area_Vol
ErrorHandler5:
Exit Sub
End Sub

 

[Jerry Sullivan]

Rob, Thanks for sharing your progress. It's great that you were able to work through that.

The control structure could be simplified to do less jumping around the procedure.

Would you be interested in that? I won't be able to get to that until later, or perhaps another helper will respond?