Search code examples
excelslicersvba

Multi-select Excel slicers

I would like to set Multi-select to be the default setup of three slicers in a dashboard.

I wrote three (one per slicer) short procedures that look like this:

Sub msel() 

 ActiveSheet.Shapes.Range(Array("Slicer1")).Select 
 SendKeys "%s" 
 SendKeys "(ESC)" 

End Sub

When invoked independently, the procedures work. When I try to invoke the three of them from another Sub, only the last Call works (i.e., only one slicer happens to be in Multi-select mode).

Any guidance would be highly appreciated.

*** something like this (see below) has the same effect:

Sub All()

Dim sCache As SlicerCache
Dim sl As Slicer

For Each sCache In ActiveWorkbook.SlicerCaches
    For Each sl In sCache.Slicers
        sl.Shape.Select
        SendKeys "%s"
        SendKeys "{ESC}"
    Next sl
Next sCache

End Sub
Solution

  • I suspect Excel is looping too fast for the SendKeys to have effect. If I add a DoEvents after your second SendKeys it works just fine for me.