• 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

M

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.
 
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.
 
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 ?
 
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
 
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
 
If you need further help, upload sample workbook with OLAP based Pivot and Slicer. I can show you how to build step by step.
 
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
 
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.
 
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.
 
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
 
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)
 
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
 
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

  • Test.xlsm
    41.2 KB · Views: 94
You must log in or register to reply here.
Back
Top