Would like to hide a filter button on a Pivot Chart

[nakedbamboo]

I have a pivot chart of a pivot table with two row fields and one column field. I would like to hide one of the row field buttons on the chart but leave the other visible to use as a filter. I see, when I right click on the button, the option to hide all axis buttons, but that removes both of them. Is there a way to only hide one? I am creating the table and chart in VBA, so if it can only be done with code that is fine.​

 

[mole999]

how about a text box applied over the top with no visible edges, just to hide it

 

[nakedbamboo]

The chart is created with VBA, so I am assuming the layout would be dynamic depending on the data in the chart. I would think it would be impossible to have code determine where and how big to create this text box each time the chart is generated. Do you think this could be performed in VBA?

 

[mole999]

it maybe worth investigating, recording in vba

also i would try your delete recorded in vba and see if the code can be identified

a third thought would be trying to change the colour of that control

 

[nakedbamboo]

I am able to hide it with a text box with code, however it is clunk in my opinion as it is not dynamic. I tried recording the other options and looking at them, but it didn't help because all the axis buttons work as one it appears. Hiding is either True or False for all and I can't find a place to modify colors (although if I could it would probably be all together as well). I will just have to stick with the text box unless someone else knows of another way. Thanks for the suggestion.

 

[mole999]

not sure if you solved this

just done in 2010

go to the analyze tab, then click on field buttons

these variants
ActiveChart.ChartArea.Select
ActiveChart.ShowReportFilterFieldButtons = False
ActiveChart.ShowLegendFieldButtons = False
ActiveChart.ShowAxisFieldButtons = False
ActiveChart.ShowValueFieldButtons = False
ActiveChart.ShowAllFieldButtons = False

 

[nakedbamboo]

I was aware of those options. You can also right click on the chart and get the same options. The problem is that they hide all buttons of their variety. When you have two row variables in a pivot chart, you get two buttons. I want to hide only one button (I want the user to be able to alter one of the variables in the chart, but not the other). If I select the option to hide, it hides both buttons. It looks like I am stuck with the cover up option. I actually switched to using a shape instead of a text box, but it is the same thing. Thanks for your help.

 

[RoryA]

You need to manipulate the source field in the pivot table:

activesheet.pivottables(1).rowfields(1).enableitemselection = false

for example. That should hide the corresponding dropdown on the chart.

 

[nakedbamboo]

Ok, this did not hide the drop down, however, it did make the drop down inactive. This works for me for now. Thanks.