Toggle Button for Power Pivot

Excel_Fool

New Member
Joined
Apr 10, 2015
Messages
15
I have the following VBA which works with a normal pivot table. It uses the text of the shape to reference the variable in the pivot table. I have rewritten it for use with a pivot table created by Power Pivot (with multiple tables), and it successfully removes all existing fields as in the first loop. However, I don't know how to write the last command (" 'Add field that button was clicked for"). Anyone know how to do this?

VBA for normal pivot table
VBA Code:
Sub Swap_Value()
'The field is determined by the button text that calls the macro.

Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String

  'Set variables
  Set pt = ActiveSheet.PivotTables(1)
  sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
  
  'Remove existing fields
  For Each pf In pt.DataFields
    If pf.Name <> "Values" Then
      pf.Orientation = xlHidden
    End If
  Next pf
  
  'Add field that button was clicked for
  pt.PivotFields(sField).Orientation = xlDataField

End Sub


VBA for Power Pivot table with multiple tables...

VBA Code:
Sub swap_values_power_pivot()

Dim ptMain As PivotTable
Dim pfMeasure As CubeField
Dim sField As String
   
  'Set variables
  Set ptMain = ActiveSheet.PivotTables(1)
  sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text

   'Remove existing fields
    For Each pfMeasure In ptMain.CubeFields
        If pfMeasure.Orientation = xlDataField Then
            pfMeasure.Orientation = xlHidden
        End If
    Next     

  'Add field that button was clicked for
  ptMain.AddDataField ptMain.CubeFields("[Measures].[" & sField & "]")

End Sub
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,223,671
Messages
6,173,736
Members
452,532
Latest member
cnetctg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top