none
Pivot Table Slicers - Limit selection

    Question

  • I have a pivot table and would like to limit the slicer selection to one or two items. The slicer has one field with 100+ items.

    ie/ "Car Number" is the field

    Is there a VBA code for this in Excel 2016? 

    Thank you in advance.

    Saturday, November 11, 2017 12:55 AM

All replies

  • Hi Cai,

    Thanks for visiting our forum.

    Then here we mainly focus on general issues about Excel client. Since your query is related to VBA code, I'll move your thread to the following dedicated MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Monday, November 13, 2017 5:29 AM
  • Hi Cai Ty,

    A slicer is a SlicerCache object, and I did not find a property or event could limit the selection item amount directly.

    Since the sheet content will be changed once selecting the items in slicer, as a workaround, I would suggest you use WorkSheet_Change event to catch the selecting action. We could use a global array to record current selected items name and then check the SlicerCache.VisibleSlicerItems.Count.

    If the visible items count<=2, we need to reset the array. 

    If the visible items count>2, iterate through the visible items and check the item name if exist in the array. If the item name is not in the array, set the item's Selected Property false.

    Here is the example code, please try to adjust it for yourself.

    Dim arr(1 To 2) As String
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Dim slc As SlicerCache
    Set slc = ActiveWorkbook.SlicerCaches("Slicer_Name")
    If slc.VisibleSlicerItems.Count > 2 Then
        For Each itm In slc.VisibleSlicerItems
        If (Not itm.Name = arr(1)) And (Not itm.Name = arr(2)) Then
        itm.Selected = False
        End If
        Next itm
    ElseIf slc.VisibleSlicerItems.Count <= 2 Then
    For i = 1 To 2
    On Error Resume Next
    arr(i) = ""
    arr(i) = slc.VisibleSlicerItems(i).Name
    Next i
    End If
    Application.ScreenUpdating = True
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, November 14, 2017 8:32 AM