Limiting Slicer multiple selection

Paul-NYS

New Member
Joined
Feb 6, 2012
Messages
23
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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
 
Upvote 0
If you want to limit the options to one, why use a slicer rather than a report filter?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 =  "[COLOR="#0000CD"]Slicer_MyNames[/COLOR]"
      
 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:
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top