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
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
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
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"
Application.OnKey "^m"