• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

change the selected slicer item based on a range of cell with vba

Maxwolf

New Member
i need help

just say i have a range cell : a2:a10 and a slicer


i would like to make a button and when we press it the slicer item change base on the content of the range cell.
 

Chihiro

Excel Ninja
Something like below.

Code:
Sub Demo()
    Dim slItem As SlicerItem
    For Each slItem In ThisWorkbook.SlicerCaches("Segment_Number_item").SlicerItems
        x = Application.Match(slItem.Name, Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row), 0)
        If IsNumeric(x) Then
            slItem.Selected = True
        Else
            slItem.Selected = False
        End If
    Next
End Sub
Though if you have OLAP based slicer it's much easier to use SlicerCaches().VisibleSlicerItemsList = Array()

And set all selected items in one shot.
 

Maxwolf

New Member
yes the code is working well. About your note easier to use SlicerCaches().VisibleSlicerItemsList = Array() , how do i retrieve the data from the user in the array ?
 

Chihiro

Excel Ninja
Basically if you have OLAP based slicer. Naming convention will be like...
[TableName].[ColumnName].&[ItemName]

And VisibleSlicerItemsList can be set like...
Code:
    ActiveWorkbook.SlicerCaches("Slicer_Date.Dates2").VisibleSlicerItemsList = _
        Array( _
        "[Date].[Dates].[Month].&[20180101]", "[Date].[Dates].[Day].&[20180201]")
See link for sample code of how to build array of SlicerItems using code. CK76 is my handle in that forum.
https://www.excelforum.com/excel-programming-vba-macros/1218210-update-slicers-with-date.html
 

Maxwolf

New Member
you are right when there is a lot of data in column 1 that take a long tieme to change in the slicer. i will try your suggestion about building the arry. i have read the exemple and i try to do the same for my exemple but it is not easy for me to do it . in your exemple it seems to have 3 field ( date). thank you for your help
 

Chihiro

Excel Ninja
If you need further help, upload sample workbook with OLAP based Pivot and Slicer. I can show you how to build step by step.
 

Maxwolf

New Member
well , is it not possible to make an iteration of the column A and then construct the array and apply it to VisibleSlicerItemsList property ? is it not possible to use the excel file that i have already upload ? like this:
Dim fltAr1 As Long
Dim baseStr
baseStr = "[Tableau croisé dynamique1].[Number item].&"
Dim rg As Range
For Each rg In Range("A1:A10")
'construction of the array
Next
ThisWorkbook.SlicerCaches("Segment_Number_Item").VisibleSlicerItemsList = fltAr1

but i do not know how to construct the array
 

Chihiro

Excel Ninja
No, it's not possible with your sample. Since your sample is using standard Excel Range as pivot table and slicer source.

In order to use .VisibleSlicerItemsList property, you need to use OLAP Data model as source for pivottable and slicer.

Otherwise, that property will result in error as it does not exist for non OLAP slicer/pivots.
 

Chihiro

Excel Ninja
Yes. But when do you want to trigger the code?

If you make it trigger with worksheet_change event, code will fire any time any value is entered in Column H.

Usually, it's safer to have user choose when to trigger code by using button or shortcut key.
 

Chihiro

Excel Ninja
Anyways, here's sample code.

Code:
Sub Bouton1_Cliquer()
    Dim ar1
    Dim strConst as String
    strConst = "[Plage].[Number item].&["
    ReDim ar1(1 To Cells(Rows.Count, "H").End(xlUp).Row - 1)
  
    For i = 1 To UBound(ar1)
        ar1(i) = strConst & Cells(i + 1, "H").Value2 & "]"
    Next
  
    ThisWorkbook.SlicerCaches("Segment_Number_item1").VisibleSlicerItemsList = ar1
End Sub
 

Maxwolf

New Member
THANK YOU very much. it is working perfectly. Only one last question if i may, what if the value input in the column H is not on the same sheet but on another sheets. How do i make the reference to the column ?

ReDim ar1(1 To Cells(Rows.Count, "H").End(xlUp).Row - 1)
 

Chihiro

Excel Ninja
Lets say you have it in sheet named "Lists". Then something like...
Code:
With Sheets("Lists")
    ReDim ar1(1 To .Cells(Rows.Count, "H").End(xlUp).Row - 1)
End With
Notice the Period "." added before Cells.

NOTE: You also need to change following portion.
Code:
    For i = 1 To UBound(ar1)
        ar1(i) = strConst & Cells(i + 1, "H").Value2 & "]"
    Next
 

JAYA TEJA

New Member
Sir,
Above code is very much useful to me but I am facing a problem while running code
Please provide same code for attachment...

Thankyou
 

Attachments

Top