Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    May 2004
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making PivotItems Visible (English/2003/SP2)

    I'm stuck on a problem. Trying to programatically unhide pivotitems, however, I can't seem to be able to list hidden items in code.

    By hidden I mean -
    -In pivot table, right click on pivotfield button
    -Select Field Settings
    -Select item to hide in the Hide Items list and click OK

    If I run this code '(All)' is returned even though there are items hidden.

    Public Sub EnumPTProdClass()

    Dim strVItems
    Sheets("ChartData").Activate

    For Each pvtItem In ActiveSheet.PivotTables(1).PivotFields("Customer") .VisibleItems
    strVItems = strVItems + pvtItem
    Next
    Debug.Print strVItems

    End Sub

    Again, the above code is not the goal, just my first step to making items visible again.

    Can anyone tell me if this is possible?

    Thanks,

    Kevin

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

    Re: Making PivotItems Visible (English/2003/SP2)

    But (All) *is* visible in the list, isn't it? This does not mean that all items are visible, but that "(All)" is displayed in the list. Turn it around:

    For Each pvtItem In ActiveSheet.PivotTables(1).PivotFields("Customer") .PivotItems
    If pvtItem.Visible = False Then
    strHiddenItems = strHiddenItems & " " & pvtItem
    End If
    Next

  3. #3
    Lounger
    Join Date
    May 2004
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making PivotItems Visible (English/2003/SP2)

    Thanks for the reply Hans. I was able to get the following to do what I need. This is based on an example in Help.

    Public Sub UnhideAllPivotItems()

    Worksheets("Sheet2").Activate

    With Worksheets("Sheet2").PivotTables(1)

    For f = 1 To .PivotFields.Count

    For i = 1 To .PivotFields(f).PivotItems.Count
    .PivotFields(f).PivotItems(i).Visible = True
    Next

    Next

    End With

    End Sub

    It would be nice though if I could figure out how to make the above only operate on items that are hidden. If I understand it correctly, it loops through everything.

    I've noticed something and I'm not sure I understand the reason. If items are within page fields, I cannot get the correct visibility to be returned. To see what I mean, make a simple pivot table, hide some of the items in one of the page fields. Run the following code. Then move the page field to a column or row field position and re-run the following code.

    Public Sub EnumPivotItemsAndVisibility()

    'PivotItems(i).Visible appears to mean only if shown in the pivot table?
    'I.e., page field items are returned as False.

    Worksheets("Sheet2").Activate

    With Worksheets("Sheet2").PivotTables(1)

    For f = 1 To .PivotFields.Count

    For i = 1 To .PivotFields(f).PivotItems.Count
    Debug.Print .PivotFields(f).PivotItems(i).Name & " " _
    & .PivotFields(f).PivotItems(i).Visible
    Next

    Next

    End With

    End Sub

    Any thoughts?

    Kevin

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

    Re: Making PivotItems Visible (English/2003/SP2)

    For a page field, the only visible item is the one currently displayed, i.e. either "(All)" or a specific item. As far as I can tell, there is no way to know which items have been hidden.

    For a row or column field, the Visible property corresponds with the hidden/not hidden state of the items.

  5. #5
    Lounger
    Join Date
    May 2004
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making PivotItems Visible (English/2003/SP2)

    Hans,

    Thanks for the information. Just so I understand, you can "write" the visible property of page fields, but you can't "read" it?

    Kevin

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

    Re: Making PivotItems Visible (English/2003/SP2)

    It appears to be that way.

Posting Permissions

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