Toolkit Add-In for Excel (Pivotfield formatting)

Option Explicit

Sub setPivotFieldType(iFieldType As Integer)
' sets the Field Type (sum, average, etc) in a selected range of pivotfields
    ' sanity test
    If TypeName(Selection) <> "Range" Then Exit Sub
    toggleStatus
    On Error Resume Next
    ' test for end of used fields; keeps from running through an entire column or row
    Dim c As Range, ptField As PivotField, intEndRow As Integer, intEndCol As Integer
    intEndRow = Selection.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    intEndCol = Selection.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column

    ' for each selected cell, get the pivotfield and set it to the field type
    For Each c In Selection
        Set ptField = c.PivotField
        If Not ptField Is Nothing Then _
            ptField.Function = iFieldType
        If c.Row > intEndRow Or c.Column > intEndCol Then Exit Sub
    Next c
    toggleStatus True
End Sub

Sub setPivotFieldFormat(strFormat As String)
' sets the format of a field (or cell if not a pivot table) according to the strFormat (in NumberFormat style)
    ' sanity test
    If TypeName(Selection) <> "Range" Then Exit Sub
    toggleStatus
    On Error Resume Next
    ' test for end of used fields; keeps from running through an entire column or row
    Dim c As Range, ptField As PivotField, intEndRow As Integer, intEndCol As Integer
    intEndRow = Selection.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    intEndCol = Selection.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column

    ' for each selected cell, get the pivotfield and set it to the field type
    For Each c In Selection
        Set ptField = c.PivotField
        If ptField Is Nothing Then
         Selection.NumberFormat = strFormat
        Else
         ptField.NumberFormat = strFormat
        End If
        If c.Row > intEndRow Or c.Column > intEndCol Then Exit Sub
    Next c
    toggleStatus True
End Sub

' FieldType buttons
Sub setPivotFieldTypeSum(Optional control As IRibbonControl)
    setPivotFieldType (xlSum)
End Sub
Sub setPivotFieldTypeAvg(Optional control As IRibbonControl)
    setPivotFieldType (xlAverage)
End Sub
Sub setPivotFieldTypeCnt(Optional control As IRibbonControl)
    setPivotFieldType (xlCount)
End Sub
Sub setPivotFieldTypeVar(Optional control As IRibbonControl)
    setPivotFieldType (xlVar)
End Sub
Sub setPivotFieldTypeStDev(Optional control As IRibbonControl)
    setPivotFieldType (xlStDev)
End Sub
Sub setPivotFieldTypeMax(Optional control As IRibbonControl)
    setPivotFieldType (xlMax)
End Sub
Sub setPivotFieldTypeMin(Optional control As IRibbonControl)
    setPivotFieldType (xlMin)
End Sub

' Format buttons
Sub setPivotFieldFormatGen(Optional control As IRibbonControl)
    setPivotFieldFormat ("0")
End Sub
Sub setPivotFieldFormatNum(Optional control As IRibbonControl)
    setPivotFieldFormat ("#,###")
End Sub
Sub setPivotFieldFormatAct(Optional control As IRibbonControl)
    setPivotFieldFormat ("_(* #,##0.0_);_(* (#,##0.0);_(* "" - ""?_);_(@_)")
End Sub
Sub setPivotFieldFormatPct(Optional control As IRibbonControl)
    setPivotFieldFormat ("0.0%")
End Sub
Sub setPivotFieldFormatCur(Optional control As IRibbonControl)
    setPivotFieldFormat ("$0.00;-$0.00")
End Sub
Sub setPivotFieldFormatSci(Optional control As IRibbonControl)
    setPivotFieldFormat ("0000E+00")
End Sub
Sub setPivotFieldFormatBig(Optional control As IRibbonControl)
    setPivotFieldFormat ("0, k")
End Sub
Sub setPivotFieldFormatWho(Optional control As IRibbonControl)
    setPivotFieldFormat ("#,##0")
End Sub
Sub setPivotFieldFormatCustom(Optional control As IRibbonControl)
    If TypeName(Selection) <> "Range" Then Exit Sub
    frm_FieldFormat.Show
End Sub