Disable pivot filter with VBA

[Binbs]

Hello,

I have a pivot table with a filter applied. I do not want users to inadvertently clear this filter.

I have been researching how to disable a pivot filter using VBA and found the following VBA code: ActiveSheet.PivotTables(1).RowFields(2).EnableItemSelection = False

This seems to be the code I need but I am having trouble implementing it due to my limited VBA experience.

The name of my pivot table is called "OL" and the filter I want to disable is on row 18. here is what I have tried:

Sub BLOCK()

Dim wks As Worksheet
ActiveSheet.PivotTables("OL").RowFields(18).EnableItemSelection = False
End Sub

This does not appear to work however. Any help with VERY appreciated.

Thank you!

 

[Binbs]

I also tried this to no avail:

Sub DesativaFiltroPivotTables()
Dim pt As PivotTable
Dim pf As PivotField

For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
pf.EnableItemSelection = False
Next pf
Next
End Sub

 

[Domenic]

Try referring to your first RowField . . .

ActiveSheet.PivotTables("OL").RowFields(1).EnableItemSelection = False

Hope this helps!

 

[Binbs]

Try referring to your first RowField . . .

ActiveSheet.PivotTables("OL").RowFields(1).EnableItemSelection = False

Hope this helps!

thanks! But no luck unfortunately,

 

[Domenic]

The name of my pivot table is called "OL" and the filter I want to disable is on row 18. here is what I have tried:

Sub BLOCK()

Dim wks As Worksheet
ActiveSheet.PivotTables("OL").RowFields(18).EnableItemSelection = False
End Sub

You need to refer to your RowFields by PivotField index number or name, not by worksheet row reference. So, for example, if you want to disable the item selection for a single PivotField in your Rows area . . .

ActiveSheet.PivotTables("OL").RowFields(1).EnableItemSelection = False

or

ActiveSheet.PivotTables("OL").RowFields("PivotFieldName").EnableItemSelection = False

I also tried this to no avail:

Sub DesativaFiltroPivotTables()
Dim pt As PivotTable
Dim pf As PivotField

For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
pf.EnableItemSelection = False
Next pf
Next
End Sub

This should disable the item selection for all PivotFields in all PivotTables within the active worksheet. After running this code, the drop down arrow for all PivotFields should disappear. Is this not the case?

 

[Binbs]

You need to refer to your RowFields by PivotField index number or name, not by worksheet row reference. So, for example, if you want to disable the item selection for a single PivotField in your Rows area . . .

ActiveSheet.PivotTables("OL").RowFields(1).EnableItemSelection = False

or

ActiveSheet.PivotTables("OL").RowFields("PivotFieldName").EnableItemSelection = False

This should disable the item selection for all PivotFields in all PivotTables within the active worksheet. After running this code, the drop down arrow for all PivotFields should disappear. Is this not the case?

Hi Domenic,

The second macro to disable all filter in the pivot does in fact work the way you said it should. Thank you very much for your comments and help.

 

[Domenic]

That's great, I'm glad you were able to work it out.

Cheers!