CODEWITHSUNDEEP
excelpivot-tableslicers
Royce
Royce

Reputation: 1595

Excel Slicer, how to allow the selection of all elements or only one

I'm trying to restrict the behavior of an Excel Slicer.

My objective is the following one : Allow the selection of all elements or only one.

My slicer has 5 elements and I don't want to that the user can select 2, 3 or 4 elements. Only 1 or 5.

Is it possible to do it ?

Thanks

Upvotes: 0

Views: 5101

Answers (2)

Jon vB
Jon vB

Reputation: 71

Hiding the Header is not enough. Crafty users will know that you can hold down control or shift to multi-select items in a slicer. You CAN achieve this with a pretty simple VBA macro.

This code will allow just ONE item to be selected - as soon as the user tries to select another, the second one (in the order they are in the slicer) will be removed and a message sent to the user.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sc As SlicerCache
    Dim si As SlicerItem
    Dim boolFoundFirstSelect As Boolean, boolMsgSent As Boolean

    boolFoundFirstSelect = False
    Set sc = ThisWorkbook.SlicerCaches("Slicer_Group_By")
    For Each si In sc.SlicerItems
        If si.Selected = True Then
            If boolFoundFirstSelect = False Then
                boolFoundFirstSelect = True
            Else
                si.Selected = False
                MsgBox "Please select only one item"
            End If
        End If
    Next
End Sub

To also allow ALL options to be selected - use the code below:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sc As SlicerCache
    Dim si As SlicerItem
    Dim boolFoundFirstSelect As Boolean
    
    boolFoundFirstSelect = False
    Set sc = ThisWorkbook.SlicerCaches("Slicer_Group_By")
    
    Dim intCountNOTSelected As Integer
    intCountNOTSelected = 0
    For Each si In sc.SlicerItems
        If si.Selected = False Then intCountNOTSelected = intCountNOTSelected + 1
        
    Next
    If intCountNOTSelected > 0 Then
        For Each si In sc.SlicerItems
            If si.Selected = True Then
                If boolFoundFirstSelect = False Then
                    boolFoundFirstSelect = True
                Else
                    si.Selected = False
                    MsgBox "Please select only one item"
                End If
            End If
        Next
    Else
        'If all of them are selected, then we'll do nothing per the OP's request
    End If
End Sub

A couple notes:

  1. This is a built-in subroutine for a worksheet in Excel - you must use the name as specified

  2. This only works for Pivot table slicers, not "table" slicers

  3. You need to put the code in the sheet where the Pivot Table resides - you can copy / move the slicer wherever you want - but the VBA needs to be in the sheet that has the pivot table on it.

  4. If you select all then UN-select one item, this code generates multiple message boxes. A bit annoying - but Excel seems to re-call that sub-routine for every filter item being kept, so a simple variable doesn't solve it.

  5. If you don't know the slicer cache name - use this:

Sub GetSlicerNames()
    Dim sc As SlicerCache
    Dim si As SlicerItem
    
    For Each sc In ThisWorkbook.SlicerCaches
        Debug.Print sc.Name
    Next
End Sub

Upvotes: 0

sophocles
sophocles

Reputation: 13841

Yes it can happen.

IIUC, you only want to give the ability to select either 1 value from the slicer, or have all selected.

Assume I have a slicer that has 5 elements 2015, 2016, 2017, 2018, 2019 like below:

enter image description here

You can select the "Multi-Select" filter to disable it, shown below:

enter image description here

If you do that, you allow the user to only select 1 year at a time, OR, the user can clear filter with the button next to it (shown with blue in picture below), to select all elements (years in this case) of the slicer.

enter image description here

I hope this addresses your question.

EDIT: One way I removed the Multi-Select button before giving to client, is that I right clicked on the Slicer, clicked Slicer Settings (the last one), and then untick Display Header. This will make your slicer look like:

enter image description here

You can then add the title of your Slicer with a text box.

An alternative way to not allow your user to click on the Multi select button, would be to right click on your Tab --> Protect Sheet --> Select the options you want him to be able to play with. However, the problem with this approach is that the user won't be allowed to click on he filters, as they will be locked too. So I would go with option 1 :)

Upvotes: 1

Related Questions