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
 
Hi bitechie,

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

Jerry,

I am using the above code in Excel 2010, and it is throwing an error "Method 'List' of object'_CommandBarComboBox' failed. Can you help me understand why this might be?

Thank you
 
Last edited by a moderator:
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi djkenny,

That error would occur if the Undo Stack was empty. If the Workbook_SheetPivotTableUpdate event was triggered by the User Interface, there should be at least one item in the Undo Stack. The Stack would be empty if the Workbook_SheetPivotTableUpdate event was triggered by other VBA code. Do you have some VBA code that could have done that?

Typically, it's best to temporarily disable events in any of your VBA code that could unintentionally trigger other events.
You can do that with the Application.EnableEvents property.

Nonetheless the code in Post #9 could be improved by adding handing of the scenario of the Undo Stack being empty.
Here's one way....

Code:
 '--handle scenario of empty undo stack
 On Error Resume Next
 sLastUndoStackItem = Application.CommandBars("Standard").FindControl(ID:=128).List(1)
 On Error GoTo 0
         
 '--validate event was triggered by slicer or filter, not other pivot operation
 Select Case sLastUndoStackItem
   Case "Slicer Operation", "Filter"
      'continue
   Case vbNullString
      '--the undo stack was empty
      '--exit or optionally take some other action
      GoTo ExitProc
   Case Else
      'do nothing and exit
      GoTo ExitProc
 End Select
 
Upvote 0
Hi bitechie,

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

Hi Jerry, I am struggling to make your code work. I pasted it into my "ThisWorkbook" VBA section. I have three slicers all of which need to be limited. They are "Entity", "Month_Name1" and "Year_Text1". Each of the three slicers must be limited to 1 selection per slicer. Must I define the Pivot table? Mine is called "PivotTable". Please help?

Love headed your way in advance!
 
Last edited by a moderator:
Upvote 0
Dear Jerry,

I use your code in excel 2013, and it is throwing an error "run time error 1004 ,Application-defined or object defined error" in line 'If slc.VisibleSlicerItems.Count > 1 Then' . i used slivcer that get data from outsource. Can you help me understand why this might be?

Thank you
 
Upvote 0
Dear FranzV
tnx for your help but I can not open this link. Do you sure that link work properly. I would be grateful if you help me.
 
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)

......
[/QUOTE]

Hi Jerry,

Thanks for the code! :)

I have more than one slicer that need to be validated for different number of buttons selected. Is there better way than just copy and paste of the code with different name (I suppose that I have to change all the variables as well, since simple change of sub is not enough)? It becomes impractical if I have eg. 10 slicers.. 

Could you help me with this issue?

Thanks,
An
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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