How to turn off drop down on Pivot table

[sstrachan]

I'm attempting to disable some of the dropdown boxes on a Pivot Table. I found a Thread by RORYA to disable all of the dropdown boxes (see below), and it works perfectly. I am, however, attempting to disable only some of the drop down boxes, not all. Is there a way to tweek the code below so that only some of the drop downs are disabled?

My Pivot table contains 8 Columns. I want to disable Columns D, E, and F, and leave all remaining drop down boxes functional. If it helps, the labels for the columns are, HC, Attainment, and Product.



Sub hidedrops()
Dim pf As PivotField, pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
On Error Resume Next
pt.ManualUpdate = True
For Each pf In pt.RowFields
pf.EnableItemSelection = False
Next pf
For Each pf In pt.ColumnFields
pf.EnableItemSelection = False
Next pf
For Each pf In pt.DataFields
pf.EnableItemSelection = False
Next pf
pt.ManualUpdate = False
End Sub

 

[c_m]

Try:

Sub hidedrops()
   Dim pf As PivotField, pt As PivotTable
   Set pt = ActiveSheet.PivotTables(1)
   On Error Resume Next
   pt.ManualUpdate = True

   For Each pf In pt.RowFields
      pf.EnableItemSelection = False
   Next pf

   For Each pf In pt.ColumnFields
if pf = "HC" or pf = "Attainment" or pf = "Product" then
      pf.EnableItemSelection = False
end if
   Next pf

      For Each pf In pt.DataFields
      pf.EnableItemSelection = False
   Next pf

   pt.ManualUpdate = False

End Sub

 

[sstrachan]

Unfortunatley, all drop downs are still disabled, not just the 3 I am trying to disable. Any other thoughts?

 

[c_m]

Unfortunatley, all drop downs are still disabled, not just the 3 I am trying to disable. Any other thoughts?

how about this:

Sub hidedrops()
   Dim pf As PivotField, pt As PivotTable
   Set pt = ActiveSheet.PivotTables(1)
   On Error Resume Next
   pt.ManualUpdate = True

   For Each pf In pt.RowFields
      pf.EnableItemSelection = True
   Next pf

   For Each pf In pt.ColumnFields
if pf = "HC" or pf = "Attainment" or pf = "Product" then
      pf.EnableItemSelection = False
end if
   Next pf

      For Each pf In pt.DataFields
      pf.EnableItemSelection = True
   Next pf

   pt.ManualUpdate = False
End Sub

 

[sstrachan]

unfortunately, no. now none of the drop downs in the pivot table columns are deactivated.

 

[c_m]

unfortunately, no. now none of the drop downs in the pivot table columns are deactivated.

Tested:

Sub hidedrops()
   Dim pf As PivotField, pt As PivotTable
   Set pt = ActiveSheet.PivotTables(1)
   On Error Resume Next
   pt.ManualUpdate = True

   For Each pf In pt.RowFields
      pf.EnableItemSelection = True
   Next pf

   For Each pf In pt.ColumnFields
   if pf = "HC" or pf = "Attainment" or pf = "Product" then
   pf.EnableItemSelection = False
   else
   pf.EnableItemSelection = True
   end if
   Next pf

   For Each pf In pt.DataFields
      pf.EnableItemSelection = True
   Next pf

   pt.ManualUpdate = False

End Sub

Notice the else condition in If statement.
This will only deactivate drop down in column field with names HC, Attainment or Product. All other fields will have drop down activated.

 

[sstrachan]

Thank You! It worked. The problem that I was having was the Columns I was trying to deactivate were in the Row Fields, not the column fields. While it is headed as a Row field, it appears in Columns. This is why I was not able to see any results with your code. I probably had not explained accurately or in enough detail for you. Once I was able to see that it was a column field that you were deactating, it was very simple to change the code to deactivate the Row Field. Sorry for the miscommunication, and thank you for your assistance. It worked PERFECTLY!!! Thanks again. For reference, I only made one slight change to what you provided. I changed for each pf In pt.ColumnFields, to for Each pf in Inpt.RowFields.

The new sub is listed below for reference.


Sub hidedrops()
Dim pf As PivotField, pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
On Error Resume Next
pt.ManualUpdate = True
For Each pf In pt.RowFields
pf.EnableItemSelection = True
Next pf
For Each pf In pt.RowFields
If pf = "HC" Or pf = "Attainment" Or pf = "Product" Then
pf.EnableItemSelection = False
Else
pf.EnableItemSelection = True
End If
Next pf
For Each pf In pt.DataFields
pf.EnableItemSelection = True
Next pf
pt.ManualUpdate = False
End Sub