How to disable select multiple items in pivot table?
When we create a pivot table and set the option of filtered items, but, you don’t want others to apply the Select Multiple Items feature in the pivot table to upset the original data in your pivot table. In this article, I will talk about how to disable this option in pivot table.
Disable select multiple items in pivot table with VBA code
Disable select multiple items in pivot table with VBA code
To protect the pivot table selection from being changing, the following VBA code can help you to disable the Select Multiple Items feature. Please do as follows:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Disable select multiple items in pivot table
Sub DisableSelection()
'Update 20141028
Dim xPF As PivotField
Set xPT = Application.ActiveSheet.PivotTables(1)
For Each xPF In xPT.PivotFields
xPF.EnableItemSelection = False
Next xPF
End Sub
3. Then press F5 key to run this code, and the filter arrow buttons in the pivot table have been removed at once, see screenshots:
 |
 |
 |
Notes:
1. If you want to enable the Select Multiple Items again, please apply the following code:
VBA code: Enable select multiple items in pivot table:
Sub DisableSelection()
'Update 20141028
Dim xPF As PivotField
Set xPT = Application.ActiveSheet.PivotTables(1)
For Each xPF In xPT.PivotFields
xPF.EnableItemSelection = True
Next xPF
End Sub
2. This code is only applied to one pivot table in the active worksheet.
Related articles:
How to hide expand collapse buttons in pivot table?
How to hide subtotals in pivot table?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
