VBA - Slicer Selection
Results 1 to 6 of 6

Thread: VBA - Slicer Selection

  1. #1
    Board Regular
    Join Date
    Apr 2015
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Slicer Selection

    I am trying to get a worksheet to only select 4 criteria from a slicer that has over 100 options in VBA.
    I tried the method of just recording a macro but that was too long of a command and quit.

    Does anybody have any code that I could adjust / alter to get me in the right direction?

    Thank you.

  2. #2
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,120
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Slicer Selection

    Hi,

    Let's say you ave 100 names and you want to select John, Peter, Bob and Kevin in your slicer "Slicer_1"
    Code:
    Dim sI As SlicerItem
       For Each sI In ActiveWorkbook.SlicerCaches("Slicer_1").SlicerItems
           If (sI.Caption = "John" or sI.Caption = "Peter" or sI.Caption = "Bob" or sI.Caption = "Kevin") Then
              sI.Selected = True
           Else
             sI.Selected = False
           End If
       Next
    Last edited by Kamolga; May 10th, 2019 at 03:16 PM.

  3. #3
    Board Regular
    Join Date
    Nov 2004
    Location
    Newport Beach, CA
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Slicer Selection

    SO my slicer contains one selector linked to 3 pivot tables.

    The selections in the slicer are numbers 0, 1, 2, 3, 4, 5.......50. I can modify this macro as shown above but would be really long.

    Before the slicer, which I created today, I would go to each pivot and filter for less than or equal to the number for the day. Say today the number is 29, I would like the macro to have the slicer select all buttons less than or equal to 29, which would set the three pivot tables to less than or equal to 29, is this possible or do I just nest the code above 50 times?

    The number is calculated elsewhere in the model, would be fantastic to get that number and SLICER set equal or less than to the calculated number.

  4. #4
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,120
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Slicer Selection

    Quote Originally Posted by Perrister View Post
    The number is calculated elsewhere in the model, would be fantastic to get that number and SLICER set equal or less than to the calculated number.
    Hi,
    When you calculate it, just name it. In this example I will name it Val and just says it equals 29 but you can use whatever calculation you want.
    Code:
    Dim Val As Long: Val = 29
    I would like the macro to have the slicer select all buttons less than or equal to 29, which would set the three pivot tables to less than or equal to 29, is this possible or do I just nest the code above 50 times?
    The captions being text, you need to convert to a number with CInt
    Code:
    Dim sI As SlicerItem
       For Each sI In ActiveWorkbook.SlicerCaches("Slicer_Test").SlicerItems
           If CInt(sI.Caption) <= Val Then
              sI.Selected = True
           Else
             sI.Selected = False
           End If
       Next
    The selections in the slicer are numbers 0, 1, 2, 3, 4, 5.......50
    Note that 'CInt(sI.Caption)' will create an error if you have 'Si.Caption' that has text that can not be converted to a number such as 'S29'

  5. #5
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Slicer Selection

    You could tie this worksheet change event to your that calculated number, which I have given a defined name of _threshold (my slicer name is Slicer_Id, adjust to suit)

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sI As SlicerItem
        Application.EnableEvents = False
        
        With Me
        
            If Not Intersect(Target, .Range("_threshold")) Is Nothing Then
            
                For Each sI In .Parent.SlicerCaches("Slicer_Id").SlicerItems
           
                    sI.Selected = CInt(sI.Caption) <= Target.Value
                Next sI
           End If
       End With
        Application.EnableEvents = True
    End Sub

  6. #6
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,120
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Slicer Selection

    I would run the macro on
    Code:
    Private Sub Worksheet_Activate()
    
    
    End Sub
    if you want to launch it on an event.

    I would also store the value in a ActiveWorkbook.Names("XXX") using Define name in formula tab. You can assign those names values without range.

    Another option (that I try to avoid) is to use a public number. On top of the module, you type
    Code:
    Public Val As Long
    and now you can use Val in your macro that assigns a value to Val (on Workbook_Open event for example) and in another macro that uses Val.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •