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 Comment

  1. Jeff Weir says:

    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!

    :-)

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

You must be logged in to post a comment.

Here's how to update your reports of company and nearly any web data: