excel enable Multi-select on slicer by default

问题:

I have slicers in my workbook that I need multi-select enabled by default. I have not been able to find any way to control this with VBA. I even tried SendKeys but it doesnt seem to be working.

Here is the current Not working Sendkeys code.

    ActiveSheet.Shapes.Range(Array("WMS")).Select
        SendKeys "%S", True
    ActiveSheet.Shapes.Range(Array("Equipment")).Select
        SendKeys "%S", True
    ActiveSheet.Shapes.Range(Array("Warehousing Skills")).Select
        SendKeys "%S", True
    ActiveSheet.Shapes.Range(Array("OCF Skills")).Select
        SendKeys "%S", True

This is selecting the slicers as intended but the sendkeys are not enabling multi-select. I have tried adding a pause before and after the sendkey command but still cannot seem to get it to work.

I even tried having the VBA select two slicer options. This did work and selected two options but it did not force multi-select to be enabled.

回答1:

I don't believe you can programatically change that Multiselect toggle as at the time I write this...that property isn't exposed to the user via VBA. Looks like you've either got to go change that setting on all slicers manually, or teach your users to hold down the CTRL key while they make their selections (which lets them make multiple selections as if that toggle was activated).

回答2:

One workaround could be that you select the slicer and then use SendKeys-method.

ActiveSheet.YOURSLICER.Select
SendKeys "%s"  || sends ALT+S key combination to toggle multiselect ON.