VBA for Setting Slicer

[charllie]

Hello All

I have created an Excel Dashboard using PivotCharts and then slicers for selections.

I am trying to create one button that will always reset my slicers back to the main set up i began with after presenting the dashboard and making various slicer suggestions during the meeting.

I can do this by using the following code, however i would like to shorten the code if possible instead of having to selecting each of the ones required as false individually.

Is there a way of doing this?

In brief i want to make one selection of the slicer as "True" and leave the remaining as "False".

    With ActiveWorkbook.SlicerCaches("Slicer_Investigating_Area")
   
        .SlicerItems("Customer Services").Selected = True
       
        .SlicerItems("Despatch").Selected = False
        .SlicerItems("Origination").Selected = False
        .SlicerItems("Packing").Selected = False
        .SlicerItems("Production").Selected = False
        .SlicerItems("Site 4").Selected = False
        .SlicerItems("Goods In").Selected = False
        .SlicerItems("Other").Selected = False
        .SlicerItems("Picking").Selected = False
        .SlicerItems("Quality").Selected = False
        .SlicerItems("Site 1l").Selected = False
        .SlicerItems("Site 2").Selected = False
        .SlicerItems("Site 3").Selected = False
        .SlicerItems("Supplier").Selected = False
        
    End With

I have searched the forum but cannot find anything that does what i need, so apologies if this has been addressed previously as i could not find it.

As always, i appreciate your help.

Thanks

Charllie

 

[mart37]

    Dim sc As SlicerCache
    Dim sli As SlicerItem
    Set sc = ActiveWorkbook.SlicerCaches("Slicer_Investigating_Area")
    For Each sli In sc.SlicerItems
        sli.Selected = IIf(sli.Name = "Customer Services", True, False)
     Next

 

[charllie]

    Dim sc As SlicerCache
    Dim sli As SlicerItem
    Set sc = ActiveWorkbook.SlicerCaches("Slicer_Investigating_Area")
    For Each sli In sc.SlicerItems
        sli.Selected = IIf(sli.Name = "Customer Services", True, False)
     Next

Hi Mart37

Thank you for the reply and code, it works exactly as required.

If i wanted to add a second slicer item from the same slicer as true how would i do that? I tried a few things like adding "or" but not working. I apologise for the extra question but thought i would be able to figure it out for my self.

Thankyou again for your help and time.

Charllie

 

[mart37]

        sli.Selected = True
        If sli.Name <> "name1" And sli.Name <> "name2" Then sli.Selected = False

 

[charllie]

sli.Selected = True If sli.Name <> "name1" And sli.Name <> "name2" Then sli.Selected = False

        sli.Selected = True
        If sli.Name <> "name1" And sli.Name <> "name2" Then sli.Selected = False

Hi Mart37,

Thank you again, that is exactly what i want.

I really appreciate your time and help. Thank you again.

Charllie

 

[charllie]

Hi Mart37

Finally finished and my code looks like this. It does exactly what i want and sets the dashboard/slicers back to a starting point.

I only have one more questions which is to do with the speed. Once i select the below macro to run it is slow and the mouse cursor flickers. What i mean is that it takes longer than a normal macro to run, its not instant. I am guessing that it may be due to me selecting a number of different slicers for the macro to activate. However, i though i would ask just in case there is a work around or when produce the code below, i did something wrong.

I have tried using the screenupdating and events functions at the start and end but they don't make a lot of difference. Any suggestions?

Sub SlicerReset()


    Dim sc1, sc2 As SlicerCache
    Dim sli As SlicerItem
    Set sc1 = ActiveWorkbook.SlicerCaches("Slicer_Years")
    Set sc2 = ActiveWorkbook.SlicerCaches("Slicer_Investigating_Area")
    Set sc3 = ActiveWorkbook.SlicerCaches("Slicer_Root_Cause_Category")
    Set sc4 = ActiveWorkbook.SlicerCaches("Slicer_Analysis_Department")
    
    For Each sli In sc1.SlicerItems
        sli.Selected = True
        If sli.Name <> "2021" And sli.Name <> "2020" Then sli.Selected = False
     Next
     
    For Each sli In sc2.SlicerItems
        sli.Selected = False
        If sli.Name <> "Site1" And sli.Name <> "Site2" And sli.Name <> "Site3" And sli.Name <> "Site4" Then sli.Selected = True
     Next
     
    For Each sli In sc3.SlicerItems
        sli.Selected = False
        If sli.Name <> "Not Upheld" Then sli.Selected = True
     Next
     
    For Each sli In sc4.SlicerItems
        sli.Selected = False
        If sli.Name <> "Site1" And sli.Name <> "Site2" And sli.Name <> "Site3" And sli.Name <> "Site4" Then sli.Selected = True
     Next
    
End Sub

Thanks

Charllie

 

[mart37]

Is by me also slow.
Alternates:

        If sli.Name = "Bakker" Or sli.Name = "Bevers" Then
            sli.Selected = True
        Else
            sli.Selected = False
        End If

sli.Selected = IIf(sli.Name = "Bakker" Or sli.Name = "Bevers", True, False)

 

[charllie]

Is by me also slow.
Alternates:

        If sli.Name = "Bakker" Or sli.Name = "Bevers" Then
            sli.Selected = True
        Else
            sli.Selected = False
        End If

sli.Selected = IIf(sli.Name = "Bakker" Or sli.Name = "Bevers", True, False)

Hi Mart37

Thank you for the alternatives.

I spent some time on this yesterday using the 3 options that you provided and cannot get any change in speed. It is still slow when running the macro.

If using the the code for changing only one slicer then it runs at lightening speed. This possibly indicates the issue is the amount of slicers i am trying to change in the code.

I am going to have a play around with it some more this week and see if i can speed it up.

The code currently looks like:

Sub SlicerReset()

    'Application.ScreenUpdating = False
    'EventState = Application.EnableEvents


    Dim sc1, sc2 As SlicerCache
    Dim sli As SlicerItem
    Set sc1 = ActiveWorkbook.SlicerCaches("Slicer_Years")
    Set sc2 = ActiveWorkbook.SlicerCaches("Slicer_Investigating_Area")
    Set sc3 = ActiveWorkbook.SlicerCaches("Slicer_Root_Cause_Category")
    Set sc4 = ActiveWorkbook.SlicerCaches("Slicer_Analysis_Department")
  
    For Each sli In sc1.SlicerItems
        sli.Selected = True
        If sli.Name <> "2021" And sli.Name <> "2020" Then sli.Selected = False
     Next
   
    For Each sli In sc2.SlicerItems
        sli.Selected = False
        If sli.Name <> "Site1" And sli.Name <> "Site2" And sli.Name <> "Site3" And sli.Name <> "Site4" And sli.Name <> "Site5" Then sli.Selected = True
     Next
   
    For Each sli In sc3.SlicerItems
        sli.Selected = False
        If sli.Name <> "Not Upheld" Then sli.Selected = True
     Next
   
    For Each sli In sc4.SlicerItems
        sli.Selected = False
        If sli.Name <> "Site1" And sli.Name <> "Site2" And sli.Name <> "Site3" And sli.Name <> "Site4" And sli.Name <> "Site5" Then sli.Selected = True
     Next
   
   
    'Application.ScreenUpdating = True
    'Application.EnableEvents = False  
   
End Sub

Thanks

Charllie