• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using a scrollbar on a pivot table with filters

Dominic Martin

New Member
Hi,

I have a pivot table that basically looks like this:

Category Description Status Filter ANOther
Cat1 jfffmfmfmfm Stat1 No jhfkfkffghfhgf
Cat1 wdfafhktfhmf Stat2 No ohgjkfghkfhgdfjh
Cat1 ghdfjhdhkdk Stat1 Yes ghfhjgfghfghdf
Cat2 hgjhgjhgjhgj Stat1 No hgfhgkffghfghf
Cat2 gjfghghdfghdf Stat2 Yes ghdjhdfjhdjdj

The layout is set to show all rows in tabular form.

I'm putting the pivot table onto a dashboard that only has space for 22 rows and the full table is about 120+ rows. So I wanted to use a vertical scrollbar; hide all but 22 rows and change the visibility as the scrollbar was used.

Thanks to the following post, I have the fundamentals of it working. In particular, Hui's Filter Pivot Sub.
http://forum.chandoo.org/threads/us...lly-on-a-simple-pivot-table-not-offset.26342/

I want to use Description as the .PivotFields.

The result is that the items did not scroll smoothly and appear/disappear one at a time. It was seemingly random as to what would be visible and what would hide. So I changed the action from .PivotItems(i).Visible = True to .PivotItems(i).LabelRange.Interior.Color = vbYellow and did a debug.print .PivotItems(i) to see what happens to the scroll items.

I think I have two problems.
  1. The pivot table is filtered by the Filter column to only show the No's. However, the Sub picks up all the Descriptions. So, it may only look like 16 items have been made visible but if you took off the filter, then there are 22 items selected.
  2. I think there's a problem with the sort order. The Pivot Table is sorted by Category for the listing but when selections are being made, they're not following the sort order that the user is seeing.
So, I need to adapt the Sub to find a way of getting Excel to ignore any items that are filtered and to use the sort order that the user sees, so that it scrolls smoothly.

I've tried using .VisibleItems instead of .PivotItems but the Description is always visible. It's just that some Descriptions have been filtered out using the Filtered column. Any suggestions?
 
Just to clarify the pivot table layout a little better.

Category | Description | Status | Filter Field | ANOther Field
Cat1 | jfffmfmfmfm | Stat1 | No | jhfkfkffghfhgf
Cat1 | wdfafhktfhmf | Stat2 | No | ohgjkfghkfhgdfjh
Cat1 | ghdfjhdhkdk | Stat1 | Yes | ghfhjgfghfghdf
Cat2 | hgjhgjhgjhgj | Stat1 | No | hgfhgkffghfghf
Cat2 | gjfghghdfghdf | Stat2 | Yes | ghdjhdfjhdjdj
 
Hi ,

Can you upload your workbook with the data and code in it ?

Narayan
Hi Narayan,

Attached is a sample of what I have at the moment.

The aim is that the scrollbar should hide/unhide items from the pivot table so that only 12 (or 22 in my real worksheet) are visible at a time. To see what's going on, I substitute the Visible True/False property with a colour. So the first 12 in the list should turn yellow and the rest red. If you move the scrollbar down 1, the first entry turns red, followed by 12 yellow, then the rest are red and so on.

The pivot table has two filters (which are required). These can be applied programmatically if it helps.

1. You'll see if you take off the filters and look at the debug output that the 12 in yellow match the 12 in the Immediate Window. When you apply the filters, there is no longer 12 in yellow because some of those yellows have been filtered out.

2. The visible pivot table is ordered by Category but the selection on the description is made alphabetically. So the 12 in yellow are not a single block. If you move Description as the first column, the order is the same and a contiguous block is selected. But that's not the way I want to lay out the pivot table.

selecting Category as the field to work on doesn't work because it doesn't scroll 1 line at a time due to the repetition of Categories.

UPDATE: Since the description is no longer unique in my sample, this isn't working like I described. If you change the PivotFields to use ID instead (which is unique), then it works as per the above.

Thanks for helping.
 

Attachments

  • pivot dashboard example.xlsm
    33.2 KB · Views: 21
Last edited:
Hi ,

I am not sure whether this is what you want.

Narayan
Thank you so much for your help.

Do you know if it's still possible to just hide the items on the pivot table, rather than the whole row? The pivot table is part of a dashboard with other things to the side of it. So as rows are hidden, parts of the dashboard would disappear.

For now I could just put the pivot table underneath everything else but the ideal would for it to be next to something and just hide the items on the pivot table.

However, this has at least given me something that works, so thanks.
 
Back
Top