Flexible macro to toggle Pivot table data fields on and off

[AndrosoSabroso]

Hi! Thanks for checking this thread
I am new to VBA and I am working on a project to create an interactive dashboard for the finantial statements of the company, spanning a couple decades of data.
I have already figured out the slicer part of the dashboard to filter dates, however, I have not been able to get a working macro for toggling on and off a data value field.
The formula I am using is the following:

Sub DataField()

If ActiveSheet.PivotTables("Test").PivotFields("Sum of Example").Orientation.Value = xlHidden Then
ActiveSheet.PivotTables("Test").AddDataField ActiveSheet.PivotTables("Test").PivotFields("Example")
Else
ActiveSheet.PivotTables("Test").PivotFields("Sum of Example").Orientation = xlHidden
End If

End Sub

The goal is to create a button with a macro able to detect wether or not the "Example" field within the "Test" pivot table is currently added as a data value field.
If it is added, then the macro should remove it, if it is not added, then the macro should add it.
Also, if there was some sort of way to make the fields name a dynamic refference tied to the buttons name, that would be fantastic! That would allow me to use the same macro for the toggling of all fields, only by changing button names.
I just got started with VBA. Any feedback is very much appreciated.
Thank you so much for your help!

 

[Domenic]

In the following example, my worksheet is set-up as follows...

1) A1:C13 contains the data

2) the same worksheet contains a PivotTable called "PivotTable1"

3) the PivotTable contains two DataFields, "Sum of Apples" and "Sum of Oranges"

4) the worksheet contains two Form buttons, which toggles their respective DataField

5) each button is named after their respective field name (ie. one button is named "Apples", and the other is named "Oranges")

6) the caption for each button is set (ie. "Hide Sum of Apples" for one, and "Hide Sum of Oranges" for the other)

Now copy and paste the following code into the code module for the sheet (right-click the sheet tab, select View Code, and copy/paste)...

Option Explicit

Public Sub toggleDataField()

    Dim targetButton As Button
    Dim targetPivotTable As PivotTable
    Dim targetDataField As PivotField
   
    Set targetButton = Me.Buttons(Application.Caller)
   
    Set targetPivotTable = Me.PivotTables("PivotTable1")

    On Error Resume Next
    Set targetDataField = targetPivotTable.DataFields("Sum of " & targetButton.Name)
    If targetDataField Is Nothing Then
        With targetPivotTable.PivotFields(targetButton.Name)
            .Orientation = xlDataField
            .NumberFormat = "#,##0"
        End With
        targetButton.Caption = "Hide Sum of " & targetButton.Name
    Else
        targetDataField.Orientation = xlHidden
        targetButton.Caption = "Show Sum of " & targetButton.Name
    End If
    On Error GoTo 0
   
End Sub

Now assign the above macro to each button (right-click the button, select Assign Macro, and select the macro). Once you done so, replace from the above macro...

Public Sub toggleDataField()

with

Private Sub toggleDataField()

Now you should be able to toggle each DataField as desired. For example, here's the result of clicking on "Hide Sum of Apples"...

Hope this helps!