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

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

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

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"

One thought on “Formatting Pivot Tables Version 3

  1. 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!

    :-)

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax