Thread: Disable Selection on Pivot Table field, multiple tables

Hi there.

I'm creating a budget spreadsheet with multiple pivot tables on each worksheet. I'd like to disable the ability to select certain fields to prevent novice users from using them by accident and getting confused.

The thing is that I already have code. And it works on almost everything. Then it just stops working. For example, on the Apr and May sheet it worked on every pivot table on the worksheet. Then on June it only worked on 2 of them. Then on July, it worked on the same 2. But it did work on all the pivot tables on each page previously. So I assume this is some kind of error or glitch. Can anyone provide any help? Or can anyone tell me how to design it to run on a selcted pivot table instead of Active Sheet?

Thanks so much!!!

The code I'm using is:

Code:

Sub DisableMonthSelection()
  
Dim pt As PivotTable

  
Set pt = ActiveSheet.PivotTables(1)

 pt.PivotFields("Month").EnableItemSelection = False

End Sub

Please enclose your code in code tags or I can't answer you...

Your code, as written, will only work on the first pivot table on the active sheet. You can either make it run on every table on the active sheet:

Code:

Sub DisableMonthSelection()
  
Dim pt As PivotTable

  
For Each pt in ActiveSheet.PivotTables

 pt.PivotFields("Month").EnableItemSelection = False
Next pt
End Sub

or have it run on the selected one:

Code:

Sub DisableMonthSelection()
  
Dim pt As PivotTable
 On Error Resume Next
Set pt = Selection.PivotTable
If not pt Is Nothing Then pt.PivotFields("Month").EnableItemSelection = False

End Sub