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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,175
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,175
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,175
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,102,666
Messages
5,488,186
Members
407,631
Latest member
Mehezabin

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top