Limiting Slicer multiple selection
Limiting Slicer multiple selection
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Limiting Slicer multiple selection

  1. #1
    New Member
    Join Date
    Feb 2012
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Limiting Slicer multiple selection

    Hi
    I have a pivot table that is linked to charts and has several slicers. Is there any way to limit the number of items a person can select in a slicer to just one? If you hold down the control key, you can select more than one slicer option, which messes up the chart numbers (to put it in non-sophisticated terms).

    Paul

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,628
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Limiting Slicer multiple selection

    Hi Paul,

    As far as I know their isn't a Slicer Property or option to control that.

    One workaround would be to use VBA to detect when more then one slicer items is selected by a user interface action then undo that action with a message.

    This can be circumvented by not enabling macros or disabling events; however it could prevent users from unintentionally selecting mulitiple slicer items.

    Just ask if you want to pursue that and I'll suggest some code.
    Using Excel 2016

  3. #3
    New Member
    Join Date
    Feb 2012
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Limiting Slicer multiple selection

    Hi Jerry
    If the VBA code would work when the Excel 2010 worksheet that it is in is uploaded to Sharepoint 2010/Excel Services (and transformed to a Excel Services web page), then I would be interested. Do you know how compatible this VBA code would be with Sharepoint?

    Paul

  4. #4
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,628
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Limiting Slicer multiple selection

    Paul,

    From this article, it appears VBA is unsupported in Sharepoint 2010 Excel Services.
    Supported and Unsupported Features

    Sorry that I don't know of any workarounds to limit your users to selecting only one slicer item.
    Using Excel 2016

  5. #5
    New Member
    Join Date
    Feb 2012
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Limiting Slicer multiple selection

    Thanks Jerry and thanks for the link.

    Paul

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,211
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Limiting Slicer multiple selection

    If you want to limit the options to one, why use a slicer rather than a report filter?

  7. #7
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,628
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Limiting Slicer multiple selection

    Hi Rory,

    At least two reasons one might choose Slicers instead of Report filters even if only one item is intended to be selected at a time:
    - Visual preference (display all items, customize graphic properties)
    - Syncing multiple pivot tables/pivot charts

    I don't have access to Sharepoint 2010 Excel Services; but I'd expect that the Report Filters would still allow users to select multiple items or (All).

    Paul, If your intent is to limit the users to modifying only 1 or 2 dimensions of the chart data, perhaps an dynamic chart linked to a flat data table would be a better fit than a PivotTable and PivotChart. Hard to tell from the link on supported features whether Sharepoint 2010 Excel Services supports Data Validation lists or named ranges that would help enable dynamic charts.
    Using Excel 2016

  8. #8
    New Member
    Join Date
    Jul 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Limiting Slicer multiple selection

    Quote Originally Posted by Jerry Sullivan View Post
    Hi Paul,

    As far as I know their isn't a Slicer Property or option to control that.

    One workaround would be to use VBA to detect when more then one slicer items is selected by a user interface action then undo that action with a message.

    This can be circumvented by not enabling macros or disabling events; however it could prevent users from unintentionally selecting mulitiple slicer items.

    Just ask if you want to pursue that and I'll suggest some code.

    Hi Jerry Sullivan,
    Could you please suggest VBA code ,as mentioned above, to disable multiple selection in slicer.
    Since iam really strange to VBA coding could you please share code to detect the selection of slicer and restrict the same with UI message if multiple selection is made.

    thanks in advance.

  9. #9
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,628
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Limiting Slicer multiple selection

    Hi bitechie,

    Here is some code for you to try. Paste the code into the ThisWorkbook Code Module of your Workbook....

    Modify "Slicer_MyNames" to match the name of your Slicer.

    Code:
    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, _
       ByVal Target As PivotTable)
    '--when pivot update event triggered, checks whether a specified slicer
    '    has more than one item selected.
    '  If so, user is warned and optionally the last action can be undone.
    
     Dim bSlicerIsConnected As Boolean
     Dim pvt As PivotTable
     Dim slc As SlicerCache
     Dim sLastUndoStackItem  As String
    
     '--modify this to match your slicer's name
     Const sSLICER_NAME As String =  "Slicer_MyNames"
          
     sLastUndoStackItem = Application.CommandBars("Standard").FindControl(ID:=128).List(1)
             
     '--validate event was triggered by slicer or filter, not other pivot operation
     Select Case sLastUndoStackItem
       Case "Slicer Operation", "Filter"
          'continue
       Case Else
          'do nothing and exit
          GoTo ExitProc
     End Select
       
     '--validate specified slicer exists
     On Error Resume Next
     Set slc = SlicerCaches(sSLICER_NAME)
     On Error GoTo 0
    
     If slc Is Nothing Then
       GoTo ExitProc
     End If
    
     '--validate pvt that triggered event is connected to specified slicer
     For Each pvt In slc.PivotTables
       If pvt.Name = Target.Name Then
          bSlicerIsConnected = True
          Exit For
       End If
     Next pvt
     
     '--test how many items selected and take action if more than one
     If bSlicerIsConnected Then
       If slc.VisibleSlicerItems.Count > 1 Then
          '--option a: only warn user
          'MsgBox "Only one item may be selected" & vbCr _
          '  & "Please undo last selection."
          
          '--option b: warn user and undo
          MsgBox "Only one item may be selected"
          With Application
             .EnableEvents = False
             .Undo
          End With
       End If
     End If
    ExitProc:
       Application.EnableEvents = True
    End Sub
    Thanks to Jeff Weir for sharing the technique of reading the undo stack to determine which action triggered the PivotTableUpdate event.
    Jeff has a nice four part series of articles on the DDOE site.
    Daily Dose of Excel Blog Archive What caused that PivotTableUpdate? Part Two.
    Last edited by Jerry Sullivan; Jul 23rd, 2014 at 03:15 AM.
    Using Excel 2016

  10. #10
    Board Regular
    Join Date
    Jul 2007
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Limiting Slicer multiple selection

    Quote Originally Posted by Jerry Sullivan View Post
    Thanks to Jeff Weir for sharing the technique of reading the undo stack to determine which action triggered the PivotTableUpdate event.
    Jeff has a nice four part series of articles on the DDOE site.
    Daily Dose of Excel Blog Archive What caused that PivotTableUpdate? Part Two.
    Thanks for the kind words, Jerry. There's a 5th part now - Daily Dose of Excel Blog Archive Broken Arrow - and more to come in future in terms of Slicers.

    Cheers

    Jeff

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com