VBA/ Checkbox to add/remove fields in pivot

[aravindhan_3]

Hi,

I need a help to have a check box to add or remove a one fields in pivot table

I have recorded this code to add the fields in pivot talbe



ActiveSheet.PivotTables("PivotTable67").AddDataField ActiveSheet.PivotTables( _
"PivotTable67").PivotFields("Market Base Pay - Avg by TS/CL"), _
"Count of Market Base Pay - Avg by TS/CL", xlCount
With ActiveSheet.PivotTables("PivotTable67").PivotFields( _
"Count of Market Base Pay - Avg by TS/CL")
.Caption = " Market Base Pay - Avg by TS/CL"
.Function = xlAverage
.NumberFormat = "#,##0"


I need to have a checkbox in another sheet, if I tick the box, this field should be added, and if I untick this fields should be removed

can someone help me which checkbox should I use with what code?

Thanks for your help in advance.
Regards
Arvind

cross post : http://www.mrexcel.com/forum/excel-questions/922554-visual-basic-applications-checkbox-add-remove-fields-pivot.html#post4434619

[shailendranr]

Hello ...

Yes we can do this .. First insert check box in excel and take a control of that , in check box control reference cell it will be displaying TRUE if you have check the check box else false....
Second... Write a code for pivot table ,,this is a vba code for pivot table
Sub pivot_table()


'VBA codes to insert pivot table


Dim pt As PivotTable
Dim ptcache As PivotCache
Dim ws As Worksheet


ThisWorkbook.Activate
Sheet1.Select


'we use pivot cache to select data to insert pt
Set ptcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("a1").CurrentRegion)
Set ws = Worksheets.Add
Set pt = ActiveSheet.PivotTables.Add(PivotCache:=ptcache, TableDestination:=Range("a1"))


With pt


.PivotFields("profile").Orientation = xlRowField
.PivotFields("Company name").Orientation = xlRowField
.PivotFields("year").Orientation = xlColumnField
.PivotFields("total salary").Orientation = xlDataField
.PivotFields("industry").Orientation = xlPageField
.DisplayFieldCaptions = False
.PivotFields("count of total salary").Caption = "sum of total salary"
.PivotFields("sum of total salary").Function = xlSum


End With


End Sub


Third...in Pivot fields give reference of check box reference,
Fourth,,,,now write on more code to select when they have checked and unchecked

Fifth...Assign this code to check box ...pretty simple

[aravindhan_3]

Hi,

Thanks for the help, need more help

for adding fields I used this


Sub Add_Field()
Dim pvt As PivotTable
Set pvt = Sheets("HIDE- (Country) Chart Data").PivotTables("PivotTable67")
With pvt.PivotFields("Market Base Pay - Avg by TS/CL")
.Orientation = xlDataField
.Function = xlAverage
.Caption = "Market Base Pay - Avg by TS/CL "
.NumberFormat = "#,##0"
.Position = 7
End With
End Sub


for remove


Sub Remove_Field()
Sheets("HIDE- (Country) Chart Data").Activate
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.PivotTables("PivotTable67").PivotFields( _
"Market Base Pay - Avg by TS/CL ").Orientation = xlHidden
End Sub


I want to have 2 check boxes, one to add field another one to remove field.

checkbox 1 = Remove, when I click this field is removed,
Checkbox 2 = Add , when I click this it will add,
but again user clicks add twice, it should not add another column, since the field is already there.

can someone help to modify this.
checkbox code

Sub MBP()
If Sheets("Charts").Range("N15").Value = "True" Then Call Remove
End Sub

[shailendranr]

No need to have two check box to add & remove

you can use one enough like if you have selected check box it will add else it will remove

[aravindhan_3]

can you help me with that code plz?