Results 1 to 9 of 9
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Pivot Table - Select All Categories (2000 SR1)

    I would like the ability to select all or deselect all selections from a pivot table category / selection. Is there a way to put a button on the pivot table to do this which allows you to select a category and then run code to select or deselect all? Just wondering??? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table - Select All Categories (2000 SR1)

    Gary, I'm not really sure what you need but have a look at this thread and see if anything helps.

    Andrew C

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Pivot Table - Select All Categories (2000 SR1)

    Andrew,

    Thanks for the reply but this is not exactly what I want. See the attached document. What I want is the ability to select or deselect all fields within a pivot table category, in this case WBSID. (I.e, Select all wbsid's, C.1, C.1.1, C.1.1 or deselect all wbsid's or any other category at one time)

    Hopefully, this makes more sense.

    Thanks for the ideas
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table - Select All Categories (2000 SR1)

    Gary,

    You must leave at least one of the fields visible. Do you rerquire a quick method of selecting the items rather than clicking on them one by one ?

    Andrew

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Pivot Table - Select All Categories (2000 SR1)

    Andrew,

    You said <<Do you require a quick method of selecting the items rather than clicking on them one by one ?>>

    That is exactly what I am trying to do. When I select a category, I can see the filter for all selections, however, have to select each one individually to get them. Some the the filters contain over a thousand selections. I would like the ability to select or de-select them all at once.

    Hope this is making more sense....
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table - Select All Categories (2000 SR1)

    Unfortunately MS have not as yet provided a simple method to do that (AFAIK).

    You would need some code and for thousands of items it might be too slow. Have you considered other options apart fron Pivot tables, such as Filtering ?

    Andrew

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Pivot Table - Select All Categories (2000 SR1)

    Ugh, I was afraid of this. I have thought of other options such as filtering, however, the users complain that this adds another level of complexity and they want to keep it simple. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Thanks for the help.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table - Select All Categories (2000 SR1)

    Try this Gary:
    <pre> Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables("PivotTable1")
    For Each itm In pvt.PivotFields("Code").PivotItems
    itm.Visible = True
    Next itm
    </pre>


    This code WILL error when it gets down to one item left in the pivot table. You have to have at least one item visible. So you can decide how you want to handle that. But this should get you started...

    You could put a condition to evaluate the itm.Caption and only deselect certain items. Then you could have toggle buttons that turn sections of your WBS on and off in the pivot instead of all or nothing (which won't really work anyways).
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Pivot Table - Select All Categories (2000 SR1)

    Thanks, I will have to digest this and then give it a try.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •