CODEWITHSUNDEEP
excelvbapivot-table
Phiking
Phiking

Reputation: 75

How do I switch from Sum of to Average on the Pivot Table?

I am creating a ComboBox with "Sum" and "Average". By that I want to switch the value of the pivot table from sum to average and vice versa.

So there are 3 columns of the Value field. And I have another code that can make each one appear based on which buttons I click. Example: If I click on 2016, only the 2016 value show up on the pivot table.

How can I change from "Sum of " to "Average"?

Private Sub ComboBox2_Change()

    Dim pt as PivotTable
    Dim vField as PivotTable

    Set pt = ActiveSheet.PivotTables("PivotTable1")

    With ActiveSheet.ComboBox2
        Select Case ComboBox2
        Case Is = "Average"
            If pt = ("Sum of 2016") Then
                  .Caption = "Average of 2016"
                  .Function = xlAverage
            End If
            If pt = ("Sum of 2017") Then
                  .Caption = "Average of 2017"
                  .Function = xlAverage
        Case Is = "Sum"
            If pt = ("Average of 2016") Then
                  .Caption = "Sum of 2016"
                  .Function = xlSum
            End If
            If pt = ("Average of 2017") Then
                  .Caption = "Sum of 2017"
                  .Function = xlSum
            End if
        End Select
    End with
End Sub

Upvotes: 1

Views: 2356

Answers (1)

Asger
Asger

Reputation: 3877

If you switch the Function of a datafield, it automatically gets a new name, so you have to change the name afterwards (not before).

You may Select Case either your Combobox.Value or your DataField.Caption.

Here's an example:

Private Sub ComboBox2_Change()
    Dim pt As PivotTable

    Set pt = ActiveSheet.PivotTables("PivotTable1")

    With pt.DataFields(1)
        Select Case ActiveSheet.ComboBox2.Value
        Case "Average"
            If .Caption = "Sum of 2016" Then
                .Function = xlAverage
                .Caption = "Average of 2016"
            ElseIf .Caption = "Sum of 2017" Then
                .Function = xlAverage
                .Caption = "Average of 2017"
            End If
        Case "Sum"
            If .Caption = "Average of 2016" Then
                .Function = xlSum
                .Caption = "Sum of 2016"
            ElseIf .Caption = "Average of 2017" Then
                .Function = xlSum
                .Caption = "Sum of 2017"
            End If
        End Select
    End With
End Sub

General hint: If you use .Function or .Caption, it belongs to the previous With statement before, which - in this code - has to be the pivot's datafield and not the combobox.

Upvotes: 1

Related Questions