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.
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?
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.
- 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.
- 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.
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?