[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Removing “ALL” option in a Pivot field

In my Pivot table report, I have placed one Pivot field with orientation “xlPageField”. (Eg: Sex, with values “M”,”F”). Normally excel provides 3 options to select in such conditions (Viz: “(All)”, “M”, “F”). I want it to display only “M” and “F”.

I am NOT looking for a workaround solution like, after selecting “All” validating it in a event and changing it.

I want it to display only “M” and “F”.
0
amaronjob
Asked:
amaronjob
  • 4
  • 3
1 Solution
 
pauloaguiaCommented:
You cannot. It's by design.

I've checked the PivotTable through VBA and the PageFields have all the normal PivotItems. Thereis no "(All)" PivotItem that you can delete or anything. Excel just adds it there. And there is also no other option to deactivate it.

Sorry it's not the answer you want but it's the only possible. (at least on XL2K)

Paulo
0
 
amaronjobAuthor Commented:
I also noticed no "(All)" PivotItem under PivotFields object, can you assure there is no OTHER tweaking possible to avoid "ALL'
0
 
pauloaguiaCommented:
I can give you 95% sure (I've learned a long away ago never say never).

I searched many of my usual references for such a tweak and found nothing. I searched google and found nothing. I searched EE and found nothing.

I didn't find any page saying that it cannot be done. However I didn't found a single reference to how it could be done either.

The only alternative I see is for you to build your own combobox (with values M and F) and code it so that when it changes value, the PivotTable is recalculated.

Would this be an option?

Paulo

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
amaronjobAuthor Commented:
I got a better suggestion from Microsoft community....I think this is the best and simple solution for my requirement...

* Disable ItemSelection for that pivotfield
* add one button near pivotfield

code for toggle button

With ActiveSheet.PivotTables("PT1").PivotFields("Sex")
..EnableItemSelection = True
If .CurrentPage = "M" Then
..CurrentPage = "F"
Else
..CurrentPage = "M"
End If
..EnableItemSelection = False
End With

0
 
pauloaguiaCommented:
Apart from the fact that this suggestion has a toggle button instead of a combo box it's basically what I was telling you about :) (if you had more than two values you'd have to go to a combobox).

Is this closed then?

If so and you don't think any of the comments here answers your problem then pleas, post a 0 point question in Community Support Topic Area with a link to this one, asking a moderator to come by, deal with the question and refund your points. Otherwise please, accept the comment you believe is closer to the answer (and please, read the guidelines for grading answers before you do so).

If there are still any questions about this then please post them here.

Paulo
0
 
amaronjobAuthor Commented:
Hi pauloaguia, Thanks for your effort in looking into this problem.

eventhough your comment/solution did not completly solve my problem, it did gave feed to my current solution.

If you are intrested in knowing how it is not complete enough, I will explain.

Many Thanks
0
 
pauloaguiaCommented:
Thank you.

Yes, I am. I allways like to have an explanation when I get a C. Around here C's usually means some very serious problems with the expert (check this: http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3)

So, yes, I am interested in knowing why this grade...

Paulo
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now