VBA slicer creation: how do I set multi-select property?

[CatyH]

Macro creates a set of slicers (about 200!) and sets position and properties as it goes. But I'm stymied: no way to force slicer to be in "multi-select" mode when it's created? Do I really need to go through each slicer after it's created and click the multi-select icon on each one? [ I did try recording macro to make the selection to multi-select on various slicers but apparently this is an invisible action that wasn't even recorded! ]

Or... is there some code to force slicer to be in multi-select mode? THANK YOU IN ADVANCE!

*************************

My current code to create a slicer, called from macro that defines the specifics for location, name, etc...

Sub AddSlicer(Name As String, SlicerName As String, Top As Integer, Left As Integer, Width As Integer, Height As Integer, Columns As Integer, Style As String, Caption As String)
'
    With ActiveWorkbook
        .SlicerCaches.Add2(ActiveSheet.PivotTables("PivotTable1"), Name).Slicers.Add ActiveSheet, , Name, Name, Top, Left, Width, Height
        With .SlicerCaches(SlicerName).Slicers(Name)
            .NumberOfColumns = Columns
            .Style = Style
            .Caption = Caption
        .DisplayHeader = True
        .SlicerCache.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData ' buttons don't display when no data
        '.SlicerCache.CrossFilterType = xlSlicerCrossFilterShowItemsWithDataAtTop ' buttons with no data DO display
        .SlicerCache.SortItems = xlSlicerSortAscending
        .SlicerCache.SortUsingCustomLists = True
        .SlicerCache.ShowAllItems = True
        
        
        
        
        End With
    End With
    
End Sub

 

[mart37]

I find on internet a workaround:

SendKeys "%s"
DoEvents

 

[CatyH]

I find on internet a workaround:

SendKeys "%s"
DoEvents

I do not understand... this is code that goes into the macro?
Thanks!

 

[mart37]

I also read that when you open your workbook the multiselect option reset to singleselect.
In the Workbook section you can use a macro to activate the multiselect option for all of your slicers.

 

[CatyH]

I also read that when you open your workbook the multiselect option reset to singleselect.
In the Workbook section you can use a macro to activate the multiselect option for all of your slicers.

this is exactly what i want to do. so... is this what you are saying? I'm just not understanding what a sendkey and doevent is/are... thanks!

Private Sub Workbook_Open()
SendKeys "%s"
DoEvents
End Sub

 

[mart37]

With Sendkeys can you simulate a key of your keyboard.
For all of your slicers you need something like this (is not working yet ):

    Dim sc As SlicerCache
    Dim sl As Slicer
    For Each sc In ActiveWorkbook.SlicerCaches
        For Each sl In sc.Slicers
            Debug.Print sl.Caption & " | " & sl.Parent.Name
            'sl.Caption = slicer header caption
            'sl.Parent.Name = worksheet name
            Worksheets(sl.Parent.Name).Select
            ActiveSheet.Shapes.Range(Array(sl.Caption)).Select
            SendKeys "%S"
            DoEvents
        Next sl
    Next sc