VBA to scroll a slicer - is it possible?

[AndyPandy27]

Hi Guys,

Does anyone know if it is possible for VBA to scroll (vertically) to the top of a Slicer?

I have a macro set-up which clears all Slicer filters in a workbook (as there are a few on a Dashboard I'm creating). This works nicely, however, for the longer Slicers, if someone has scrolled half-way down the list of values, once the macro has cleared the filter, the Slicer is still shown at the last point the user scrolled.

I think it would be *that* much more professional if the clear filters macro could also scroll all the Slicers back to the top.

No worries if it's not possible though - just thought I would check, as I've had no luck after a good while of searching around!

Cheers,

AP

 

[Worf]

Hi

Try this:

Sub SetSlicerToFirstItem()
' by Jan Karel Pieterse
Dim oActive As Worksheet, oSi As SlicerItem
Dim oSc As Slicer, oPt As pivottable, oSh As Worksheet
Set oActive = ActiveSheet
For Each oSh In Worksheets
    oSh.Activate
    For Each oPt In oSh.PivotTables
        For Each oSc In oPt.Slicers
            For Each oSi In oSc.SlicerCache.SlicerItems
                oSi.Selected = True
            Next
            ActiveCell.Activate
            'Prepare to send some keystrokes
            SendKeys "{TAB}{HOME}"
            'Select the slicer object on the worksheet
            oSh.Shapes(oSc.Caption).Select
            'Now send the keys to that object
            DoEvents
        Next
    Next
Next
oActive.Activate
End Sub

 

[pella88]

Extremely good solution, but is it possible to amend it to only work on specific slicer, for example ActiveWorkbook.SlicerCaches("Slicer_A")?

I would like to put your code in separate module and then call it on certain slicer which I need, so I would set it up as a sub with required variables and then forward the variables in way: call setslicertofirstitem(slicer_A).

 

[Worf]

Like this:

Sub Main()
OnlyOne "Category"
End Sub


Sub OnlyOne(sn$)
Dim sc As SlicerCache, si As SlicerItem
Set sc = ActiveWorkbook.SlicerCaches(sn)
For Each si In sc.SlicerItems
    si.Selected = 1
Next
SendKeys "{TAB}{HOME}"
sc.Slicers(1).Shape.Select
DoEvents
End Sub

 

[RapperAZEE]

Can you please elaborate what do you mean by "Category"?
And how can I call this macro in Workbook_Open event? Please help.

 

[Worf]

Welcome to the forum

In that example, category is the slicer name; see below code that lists all names, and the event macro.

Sub Info()
Dim sc As SlicerCache
For Each sc In ActiveWorkbook.SlicerCaches
    MsgBox sc.Name
Next
End Sub


Private Sub Workbook_Open()
Dim sc As SlicerCache, si As SlicerItem
Set sc = Me.SlicerCaches("slicer_division")     ' desired slicer
For Each si In sc.SlicerItems
    si.Selected = 1
Next
SendKeys "{TAB}{HOME}"
sc.Slicers(1).Shape.Select
DoEvents
End Sub

 

[RapperAZEE]

sc.Slicers(1).Shape.Select

In the above line what does 1 indicate?

 

[Worf]

It indicates the first object in the collection of slicers associated with that slicer cache. This collection can have one or several slicers.