How to turn off drop down on Pivot table

sstrachan

New Member
Joined
Mar 22, 2009
Messages
42
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

c_m

Well-known Member
Joined
May 29, 2008
Messages
836
Try:
Code:
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

New Member
Joined
Mar 22, 2009
Messages
42
Unfortunatley, all drop downs are still disabled, not just the 3 I am trying to disable. Any other thoughts?
 

c_m

Well-known Member
Joined
May 29, 2008
Messages
836
Unfortunatley, all drop downs are still disabled, not just the 3 I am trying to disable. Any other thoughts?

how about this:
Code:
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

New Member
Joined
Mar 22, 2009
Messages
42

ADVERTISEMENT

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

c_m

Well-known Member
Joined
May 29, 2008
Messages
836
unfortunately, no. now none of the drop downs in the pivot table columns are deactivated.

Tested:

Code:
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.
 
Last edited:

sstrachan

New Member
Joined
Mar 22, 2009
Messages
42
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,278
Members
416,086
Latest member
CaptainGD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top