Hi,
Hope you are good today! I need help for one of problem I am facing.
I wanted to Keep buttons to add fields to Rows, Buttons to add Columns & buttons to add value fields in pivot. the codes in the attached file works fine. When button is clicked once, it adds and removes when it click again. This works well for Rows and column fields, but not working for Value fields, it keeps adding same field again n again, as pivot tables are meant for that when it comes for values.
I am sure there would be a work around for that. Please help. attached the reference file as well.
got this code fromSub Toggle_value_Field()'Add/Remove the field to the pivot table. 'The field is determined by the button text that calls the macro. Dim pt As PivotTable Dim pf As PivotField Dim sField As String Dim shp As Shape 'Set variables Set pt = ActiveSheet.PivotTables(1) Set shp = ActiveSheet.Shapes(Application.Caller) sField = shp.TextFrame.Characters.Text 'Toggle field 'If visible then hide it If pt.PivotFields(sField).Orientation = xlDataField Then pt.PivotFields(sField).Orientation = xlHidden shp.Fill.ForeColor.Brightness = 0.5 Else 'Add to Rows area pt.PivotFields(sField).Orientation = xlDataField shp.Fill.ForeColor.Brightness = 0 End If End Sub
Author: Jon Acampora Source: https://www.excelcampus.com/vba/macr...-table-fields/ Free Newsletter: http://www.excelcampus.com/newsletter