VBA: to filter a slicer via a cell value

[matteodalla]

Hi all,
I have a question for a topic which has been covered in a lot of threads, but still even with that help, it seems not to be working in my case.
The case is simple: I have a chart, connected to a slicer which is in turn connected to a pivot.
The pivot is not on the same tab where the slicer and the charts are (don't know if this matters actually).
The slicer is correctly connected to the pivot, meaning it works fine.
I want the value of cell AO14 to be the input to filter for the field "material" in the the slicer (slicer_material).
And I'd like the selected value of the slicer to be visible at the beginning/top of the slicer.
I tried with a lot of properties of slicer.cache and slice.items but I am stuck with this simple request.
Can somebody help me out?
Thanks in advance,

Matt

 

[Worf]

• I would use an ActiveX scroll bar instead.

• Assign the code below to the form control and it should work.

Sub ScrollChange()
Dim t, pf As PivotField, pi As PivotItem
t = Array("6:30", "7:30", "8:30", "9:30", "10:30", "11:30", "12:30", "13:30", "14:30", _
"15:30", "16:30", "17:30", "18:30", "19:30", "20:30", "21:30", "22:15")
Set pf = ActiveSheet.PivotTables("PivotTable3").PivotFields("Hour")
pf.ClearAllFilters
For Each pi In pf.PivotItems
    If pi.Caption = t(ActiveSheet.Shapes("Scroll Bar 3").DrawingObject.Value - 1) Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next
End Sub

 

[vhookup]

Hi Thanks for the tweak tot he code again. Just to clarify, this code should be placed in Module 1, is that correct?

I ran it and got this result: (it only threw up the error msg)



I noticed the name of the Pivot table was noted as "3" not "1" so i changed it and it filtered the pivot table -But it cycled through the whole set of rows, it wasn't dependent on the scroll bar position. once the macro had run it came up with an error message.



I'm going to try ActiveX slider, to see if there is a difference. I will report back.

 

[Worf]

This is the code for the ActiveX scroll bar; replace the names and indexes where necessary.
Would you like a link to my test workbook to see it working?

' sheet module
Private Sub ScrollBar1_Change()
Dim t, pf As PivotField, pi As PivotItem
t = Array("6:30", "7:30", "8:30", "9:30", "10:30", "11:30", "12:30", "13:30", "14:30", _
"15:30", "16:30", "17:30", "18:30", "19:30", "20:30", "21:30", "22:15")
Set pf = ActiveSheet.PivotTables("PivotTable3").PivotFields("Hour")
pf.ClearAllFilters
For Each pi In pf.PivotItems
    If pi.Caption = t(Me.ScrollBar1 - 1) Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next
End Sub

 

[vhookup]

Hi Yes please, a link would be fantastic. As I still get it going through all the filters in one go and not a step by step. I've changed properties in the scrollbar to see if this changed things but it did not.

 

[Worf]

This workbook has an ActiveX scroll bar with the code from post 23:

https://www.dropbox.com/s/m9twhgzejruybnl/scroll.xlsm?dl=0

 

[vhookup]

Thanks Worf, I got it to work! I had to change notations of the times from e.g. 6:30 to 06:30:00, then it worked.

Functionally it works, i get the right data filtered to an end result which is ok. Is there anyway we could refine the process to give a more polished result. Currently the effect I am getting upon scrolling is a flashing colour as it runs through the criteria of the code. Is there a way for it to be modified to just show the end result and not the calculations its processing? I will try and upload a video of what i mean. I'm not too fussed if there are 'tenths' of a second delay when clicking on the scrollbar to the correct colour being displayed in the cells, as long as there aren't flashing colours.

(a quick note on what will be shown in the video):

I have a separate worksheet which will have a graphic overlay on it, I moved the scroll bar that controls the pivot table to this page, it contains merged cells manipulated to closely resemble the shapes and areas that I need, the merged cells draw in data from the result of the filtered pivot table that we have created and the cells are conditionally formatted accordingly.

https://drive.google.com/file/d/1_ib5ezwEfXCpP6fFGbFB8vLaS763xIFa/view?usp=sharing

Thanks again for all your awesome help!

 

[vhookup]

So,

I've managed to sort out the flickering of the macro, but its pretty slow. Is there any way to speed it up? I used the code below:



Private Sub ScrollBar1_Change()
Dim t, pf As PivotField, pi As PivotItem
Application.ScreenUpdating = False
t = Array("06:30:00", "07:30:00", "08:30:00", "09:30:00", "10:30:00", "11:30:00", "12:30:00", "13:30:00", "14:30:00", _
"15:30:00", "16:30:00", "17:30:00", "18:30:00", "19:30:00", "20:30:00", "21:30:00", "22:15:00")
Set pf = Sheet3.PivotTables("Pivot1").PivotFields("Hour")
pf.ClearAllFilters
For Each pi In pf.PivotItems
If pi.Caption = t(Me.ScrollBar1 - 1) Then
pi.Visible = True
Else
pi.Visible = False
End If


Next
Application.ScreenUpdating = True
End Sub

 

[vhookup]

Here's the solution I have found now, it seems to work at nice speed. Thought i'd share.

Thanks very much for your input, it's been very helpful!!

Private Sub ScrollBar1_Change()Dim t, pf As PivotField, pi As PivotItem
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
t = Array("06:30:00", "07:30:00", "08:30:00", "09:30:00", "10:30:00", "11:30:00", "12:30:00", "13:30:00", "14:30:00", _
"15:30:00", "16:30:00", "17:30:00", "18:30:00", "19:30:00", "20:30:00", "21:30:00", "22:15:00")
Set pf = Sheet3.PivotTables("Pivot1").PivotFields("Hour")
pf.ClearAllFilters
For Each pi In pf.PivotItems
    If pi.Caption = t(Me.ScrollBar1 - 1) Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If


Next
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
End Sub