VBA - Slicer Selection

[jamesmev]

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.

 

[Kamolga]

Hi,

Let's say you ave 100 names and you want to select John, Peter, Bob and Kevin in your slicer "Slicer_1"

Dim sI As SlicerItem
   For Each sI In ActiveWorkbook.SlicerCaches("[COLOR=#ff0000]Slicer_1[/COLOR]").SlicerItems
       If (sI.Caption = "John" or [COLOR=#222222][FONT=Verdana]sI.Caption = "Peter" or [/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#222222][FONT=Verdana]sI.Caption = "Bob" or [/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#222222][FONT=Verdana][COLOR=#222222][FONT=Verdana]sI.Caption = "Kevin") [/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR]Then
          sI.Selected = True
       Else
         sI.Selected = False
       End If
   Next

 

[Perrister]

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.

 

[Kamolga]

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.

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

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'​

 

[theBardd]

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)

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

 

[Kamolga]

I would run the macro on

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

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.

 

[Perrister]

If the calculated number is in a cell, then how do I get that dynamically calculated number into the Val, do I cell address? Sorry if I sound so newbie, but this is my weakness. Most of my programming is record modify defining and set I am learning.

How to have this statement pickup calculated number?

Dim Val As Long: Val = 29 (if the 29 is in cell B1 on a sheet, calculated each day)

Thanks for your help.

 

[Kamolga]

Dim Val as long: Val=Activesheet.Range("B1").value
or

Dim Val as long: Val=Worksheets("Sheet1").Range("B1").Value
if it is on sheet1

 

[Perrister]

Thank you so much, long live the board!!!!