• 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 actually on a simple Pivot table (not offset)

JCTalk

Member
Hi Guys,

I have a requirement to add vertical scrollbars to some very simple pivots I'm adding into a dashboard. Basically category and count columns only.

I've seen it done on dates horizontally (http://blog.contextures.com/archives/2013/02/28/update-pivot-table-with-scroll-bar/), I believe by filtering in some way, but I can't figure out how to use the same method to make it scroll/filter a pivot vertically with regular data.

I've uploaded my sample book, and highlighted the area I'd like to be shown on the screen.

Are there any ninja's that fancy a challenge and could help me out?

Thanks guys.
 

Attachments

  • PivotScrollSample.xlsm
    14.7 KB · Views: 90
Code:
Sub Filter_Pivot()
Dim i As Integer

Application.EnableEvents = False

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
   
  On Error Resume Next
  For i = 0 To .PivotItems.Count
  If i >= [a2] And i <= [a2] + 9 Then
  .PivotItems(i).Visible = True
  Else
  .PivotItems(i).Visible = False
  End If
  Next
 

End With
Application.EnableEvents = True
   
End Sub

See attached file:
 

Attachments

  • PivotScrollSample.xlsm
    22.7 KB · Views: 339
See attached file:

Hi Hui,

Thats fantastic! Brilliant work as usual. :)

If I wanted to apply this logic to other pivots, I notice the VBA refers directly to one pivot. I presume I need to duplicate the VBA but call each sub a different name?

Is there a way of having a generic VBA entry that doesn't specify a particular pivot table?

I'm fine duplicating, its just theres a few pivots that need this functionality, so wanted to see if I could streamline for efficiency.

Many thanks Hui. Expect a donation coming your way. :)
 
You will see that it is operating on Pivot table 1 here
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")

That could be modified to do all PT's on a worksheet or to loop through a number of them
The PivotFields can also be changed in that scenario if required
 
You will see that it is operating on Pivot table 1 here
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")

That could be modified to do all PT's on a worksheet or to loop through a number of them
The PivotFields can also be changed in that scenario if required

Hi Hui,

I have noticed some strange behaviour. When there is more than one of the same category type, the scroll / filter doesn't seem to follow the rules. It makes the items shown reduce. (New file attached)

Also, I need to show the count of number in largest to smallest order. I thought this might be done by altering the VBA PivotFields("Category") bit to PivotFields("Count of Number"), and then manually starting the sort on count of number to largest to smallest. This didn't work.

Do you know how I can do this?

Many thanks
 

Attachments

  • PivotScrollSample.xlsm
    21.9 KB · Views: 67
Hi:

Please find the attached.

I have introduced a tab to control scroll bar and couple of helper columns to get the visual effect. you just need one line of code to get the scroll bar to work. Currently I have asked the scroll bar to display only 5 items at a time, you can change it as per your need the yellow colored cell in the scroll bar control tab is user entry.

Thanks
 

Attachments

  • PivotScrollSample.xlsm
    25.5 KB · Views: 330
Back
Top