VBA for Setting Slicer

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
981
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".

VBA Code:
    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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
    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

Well-known Member
Joined
Apr 6, 2005
Messages
981
VBA Code:
    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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
        sli.Selected = True
        If sli.Name <> "name1" And sli.Name <> "name2" Then sli.Selected = False
 

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
981

ADVERTISEMENT

sli.Selected = True If sli.Name <> "name1" And sli.Name <> "name2" Then sli.Selected = False
VBA Code:
        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

Well-known Member
Joined
Apr 6, 2005
Messages
981
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?

VBA Code:
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Is by me also slow.
Alternates:
VBA Code:
        If sli.Name = "Bakker" Or sli.Name = "Bevers" Then
            sli.Selected = True
        Else
            sli.Selected = False
        End If

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

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
981
Is by me also slow.
Alternates:
VBA Code:
        If sli.Name = "Bakker" Or sli.Name = "Bevers" Then
            sli.Selected = True
        Else
            sli.Selected = False
        End If

VBA Code:
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:

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,983
Messages
5,639,380
Members
417,084
Latest member
elitepaper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top