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

 

[matteodalla]

Worf, I solved it: I wrote a macro working on the pivot table associated to it.
Thanks anyway!
M

 

[ThanhPX]

Worf, I solved it: I wrote a macro working on the pivot table associated to it.
Thanks anyway!
M

Hi Matteo,
Could you please share your solution?
Thanks in advance!

 

[Worf]

If the pivot table is filtered by code, no slicer is needed:

Sub PFilter()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("pivot7")
' uses cell D16
pt.PivotFields("Region").PivotFilters.Add2 Type:=xlCaptionEquals, Value1:=[d16]
End Sub

 

[vhookup]

Hi, I'm curious as to the solution for this thread.

I am working on a similar problem and I believe that the answer to the issue i'm facing is here. I tried adapting the code in this thread but I can only get it to clear all filters.

I have a pivot (pictured below) and I need to filter this pivot depending on the position of the scroll bar, basically the end goal dictates that I need to have this pivot filtered by a scroll bar. I have tried multiple methods including ActiveX, recording macros and various code and have not found a reliable (and quick method). I would like it to change quickly and easily so it is user friendly via the scroll bar. The first picture is how the table looks unfiltered. (this is based on "sheet3") Any help greatly appreciated.

[/URL][/IMG]


I am looking to get the table to filter based on the scroll bar position like so: (position 1 = 06:30, position 2 = 07:30 and so on all the way through to the 17th position)

[/URL][/IMG]

[/URL][/IMG]

 

[vhookup]

Sub Macro1()
'
' Macro1 Macro
'


'
Range("A26").Select
ActiveCell.FormulaR1C1 = "1"
ActiveWorkbook.SlicerCaches("Slicer_Hour").ClearManualFilter
With ActiveWorkbook.SlicerCaches("Slicer_Hour")
.SlicerItems("06:30:00").Selected = True
.SlicerItems("07:30:00").Selected = False
.SlicerItems("08:30:00").Selected = False
.SlicerItems("09:30:00").Selected = False
.SlicerItems("10:30:00").Selected = False
.SlicerItems("11:30:00").Selected = False
.SlicerItems("12:30:00").Selected = False
.SlicerItems("13:30:00").Selected = False
.SlicerItems("14:30:00").Selected = False
.SlicerItems("15:30:00").Selected = False
.SlicerItems("16:30:00").Selected = False
.SlicerItems("17:30:00").Selected = False
.SlicerItems("18:30:00").Selected = False
.SlicerItems("19:30:00").Selected = False
.SlicerItems("20:30:00").Selected = False
.SlicerItems("21:30:00").Selected = False
.SlicerItems("22:15:00").Selected = False
End With
End Sub

This code works to filter to 06:30 but its very slow and will hamper the user experience of the Dashboard. Any advice on how would I write the code so it can filter through all dates at each individual click would be super helpful, it seems to me a "Recorded Macro" isn't the best way (but the only way I know). I tried a lot of ways without slicers previous to this and had a similar result, I couldn't get the filtering to stop between each time, so on every click it filtered through all the rows and wasn't scroll bar position specific.

 

[Worf]

Hi

' 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("PivotTable5").PivotFields("time")
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 Worf,

Thanks for the reply, I was itching to try this out once i got the reply.

I have loaded the code in but its not working, I have put it in modules and Sheet3 (excel Objects) in VBA after changing the names of certain items (pivot table and scroll bar etc) the code I have is below. Any idea why it doesn't work? do i actively need to assign something somewhere or should this automatically pick it up?

Names of items:

PivotTable1
Scrollbar3
Pivot Field name is "Hour" I tried it with "time" in as well but it didn't work either.

Any Ideas?

[/URL][/IMG]

Obviously I would love the VBA to work as this is the neatest option but:

As a backup plan, I replicated the pivot table by using cell references (=a5 all the way to =J43 to replicate the pivot) then used Vlookup (to reference the position of the slider with a list of the times) then Index, Match (to look up the Vlookup result, and draw in the figures from the cloned pivot t) - this works for some lines but in others it returns #N/A, google says this is because excel doesn't recognise that cell holds any data (it seems to have one row with data then 2 without in a pattern all the way through the day) Any thoughts why this doesn't pick up the data? All cells are formatted the same too! Oh, I also created a vlookup to use instead of the index match which had the same result. Pictures below: Times in bold show #N/A others have data

[/URL][/IMG]

[/URL][/IMG]

 

[Worf]

Please run the test code below and report the results; it will tell if the scroll bar is either a form or an ActiveX control.
It will also confirm the row field name.
Note that the event code goes in the sheet module. After correctly implemented, it will work automatically.

Sub Bars_Pivots()
Dim ws As Worksheet, s$, i%, pt As PivotTable
Set ws = ActiveSheet
s = ""
For i = 1 To ws.OLEObjects.Count
    s = s & ws.OLEObjects(i).Name & vbLf
Next
MsgBox s, 64, "OLE"
s = ""
For i = 1 To ws.Shapes.Count
    s = s & ws.Shapes(i).Name & vbLf
Next
MsgBox s, 64, "Shapes"
Set pt = ws.PivotTables("PivotTable1")
s = ""
For i = 1 To pt.RowFields.Count
    s = s & pt.RowFields(i).Name & vbLf
Next
MsgBox s, 64, "Row"
End Sub

 

[vhookup]

Hi Thanks for that, I wasn't quite sure what you meant so this is what i did - I put the code in sheet module 1, then assigned the macro to the scroll bar i was using for the pivot table control, then clicked on the scroll bar -which was form, not activeX

This is the result in information boxes

1st



2nd came this,




3rd was this

 

[vhookup]

I have 3 slicers on the page - Sheet Date and Hour

Two scroll bars which were experiments for the different ways of trying to get this to work. Scroll bar 3 is the one i want to use for the code method Scroll bar 2 is the one i am using for the formula based solution.