Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Fields (XL2000 SR1)

    Pivot Table column fields have a drop down list so that selected fields only can be displayed. If there are many fields and you want to display only a few of them, it is very tedious unchecking dozens of boxes. Is it possible to write a macro to uncheck the whole list so you can then select the few entries you do want to display?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Fields (XL2000 SR1)

    In Excel 2002, the first item in the list is "(Show All)", you can use this to check/uncheck all items. I assume this is not available in Excel 2000.

    You can't uncheck the whole list in a macro, at least one item must remain visible. (You can uncheck the whole list interactively, of course, but clicking OK causes an error message then.)

    Perhaps you can adapt the following macro to your needs. I haven't done anything to generalize it.

    Sub ClearAllPivotItemsExceptOne()
    Dim i As Integer
    ' Replace "PivotTable1" and "Month" by the appropriate names
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Month")
    .PivotItems(1).Visible = True
    For i = 2 To .PivotItems.Count
    .PivotItems(i).Visible = False
    Next i
    End With
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Fields (XL2000 SR1)

    Hans, "Show All" is not available in XL2000.

    I was hoping the macro would uncheck all boxes but leave the list open so you can check one or more before pressing OK, but I guess that is probably not possible; perhaps unchecking all except the first field would be a more realistic aim.

    Thanks for your reply.

  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 Fields (XL2000 SR1)

    Michael,

    I am attaching workbook which uses a rough and ready userform to attempt to achieve what you want. When you invoke the userform you can select the Pivottable, the Field you wish to work with, and then select items individually or in extended mode using the control key. You can also "Select All", but cannot proceed unless there is at least one visiblle item in the field.

    Perhaps you can adapt it to your needs.

    Andrew C

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Fields (XL2000 SR1)

    Excellent, that seems to be just what I want.

    Many thanks Andrew.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Fields (XL2000 SR1)

    Your solution does just what I want Andrew, however I cannot get it to work from Personal.xls.

    I have dragged a copy of the form into the forms section of Personal.xls, and copied and pasted all the code into a blank module in Personal.xls, then deleted your file so there is only one copy of the form and macro left.

    When I go into my sheet with a Pivot Table and call Macros from the Tools menu, the macro appears as

    Personal.xls!'[Module1 (2)].HidePivItems

    and the Run button is grayed out, whilst other macros appear without the module name in their names and with the Run button not grayed out.

    If I edit the module name out of the HidePivItems macro, the Run button becomes active but when I press it I get the error message

    Ambiguous name detected: HidePivItems

  7. #7
    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 Fields (XL2000 SR1)

    Michael,

    It seesm to me that you have more than one instance of HidePivItems on your system and that is why the macro name includes the workbook and module name. Search your entire Personal.xls VBA Project for HidePivItems, or make sure that Personal.xls does not contain any reference to any file with that sub name.

    Ii have tried running it from Personal.xls without any problems.

    Andrew

Posting Permissions

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