Formatting Pivot Tables Version 3

I’ll try to tone down the hyperbole from the last post. It all started with a simple macro to format the selection with the comma style.

Sub MakeComma()
   
    If TypeName(Selection) = “Range” Then
        Selection.Style = “Comma”
    End If
   
End Sub

Sub MakeComma()

If TypeName(Selection) = “Range” Then

Selection.Style = “Comma”

End If

End Sub

I loved this little macro. Then I decided that formatting pivot tables was just as tedious as applying styles. I made Version 2, and I saw that it was good.

Sub MakeComma()
   
    Dim pf As PivotField
   
    If TypeName(Selection) = “Range” Then
        On Error Resume Next
            Set pf = ActiveCell.PivotField
        On Error GoTo 0
       
        If pf Is Nothing Then
            Selection.Style = “Comma”
        Else
            pf.NumberFormat = “#,##0.00″
        End If
    End If
   
End Sub

Sub MakeComma()

Dim pf As PivotField

If TypeName(Selection) = “Range” Then

On Error Resume Next

Set pf = ActiveCell.PivotField

On Error GoTo 0

If pf Is Nothing Then

Selection.Style = “Comma”

Else

pf.NumberFormat = “#,##0.00″

End If

End Sub

The other day I was making a pivot table that used the Count aggregation. I don’t use Count anywhere near as much as Sum, but there I was. Showing two decimal places with Count isn’t the worst thing in the world, but I didn’t like it. Counting is done with whole numbers. Nor did I like changing the number format manually. Version 3 was born.

Sub MakeComma()
    
    Dim pf As PivotField
    
    Const sNODECIMALS As String = "#,##0"
    Const sTWODECIMALS As String = "#,##0.00"
    
    If TypeName(Selection) = "Range" Then
        On Error Resume Next
            Set pf = ActiveCell.PivotField
        On Error GoTo 0
        
        If pf Is Nothing Then
            Selection.Style = "Comma"
        Else
            If pf.NumberFormat = sTWODECIMALS Then
                pf.NumberFormat = sNODECIMALS
            Else
                pf.NumberFormat = sTWODECIMALS
            End If
        End If
    End If
    
End Sub

Sub MakeComma()

Dim pf As PivotField

Const sNODECIMALS As String = "#,##0"

Const sTWODECIMALS As String = "#,##0.00"

If TypeName(Selection) = "Range" Then

On Error Resume Next

Set pf = ActiveCell.PivotField

On Error GoTo 0

If pf Is Nothing Then

Selection.Style = "Comma"

Else

If pf.NumberFormat = sTWODECIMALS Then

pf.NumberFormat = sNODECIMALS

Else

pf.NumberFormat = sTWODECIMALS

End If

End Sub

My initial revision checked whether the PivotField.Function property was xlSum or xlCount and applied formatting appropriately. That felt too rigid. Once my fingers are on Ctrl+M, it really doesn’t matter to me how many times I have to press down (within reason of course). So I went with a toggle between two decimals and no decimals. Now I’m happy again.

To assign to a shortcut key, I have these two statements in my Auto_Open and Auto_Close procedures, respectively.

Application.OnKey "^m", "MakeComma"
Application.OnKey "^m"

1 2	Application.OnKey "^m", "MakeComma" Application.OnKey "^m"

One thought on “Formatting Pivot Tables Version 3”

Jeff Weir says:

January 21, 2014 at 3:49 pm

My initial revision checked whether the PivotField.Function property was xlSum or xlCount and applied formatting appropriately. That felt too rigid. Once my fingers are on Ctrl+M, it really doesn’t matter to me how many times I have to press down (within reason of course).

That’s hilarious. You love keyboard shortcuts so much, that your approach requires you to use two shortcuts instead of one.

Yep, you’re a keyboard shortcut addict, alright!

:-)