VBA - Slicer Selection

jamesmev

Board Regular
Joined
Apr 9, 2015
Messages
141
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,172
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("[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
 
Last edited:

Perrister

Board Regular
Joined
Nov 30, 2004
Messages
83
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

Well-known Member
Joined
Jan 28, 2015
Messages
1,172
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'
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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
 

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,172
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,947
Messages
5,483,837
Members
407,415
Latest member
Anton1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top