Limiting slicer selection to 1 item for two different slicers

Hi Everyone,

- I am trying to get to iSlicers to limit their selection to only one item at a time. i have gotten it done for one of the islicers but cant seem to introduce the same for the other one.
- The code i am using is as follows:

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_Exp"

 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 "Selection Limited to either OPEX or CAPEX, but not both." & vbCr _
      '  & "Please undo last selection."
      
      '--option b: warn user and undo
      MsgBox "Selection Limited to either OPEX or CAPEX, but not both."
      With Application
         .EnableEvents = False
         .Undo
      End With
   End If
 End If
ExitProc:
   Application.EnableEvents = True
End Sub

- As you can see in the above code, the name of my slicer is "Slicer_Exp", and it works perfectly fine.
- Is there anyway i could introduce another slicer i have called "Slicer_M." to this code or would i have to create a code in another module?

Thanks,

Abdulla

This is "Air Code." I have nothing to test it on.

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.
 
CheckSlicer "Slicer_Exp", "Selection Limited to either OPEX or CAPEX, but not both."
CheckSlicer "Next_Slicer", "Next message"

End Sub

Sub CheckSlicer(sSlicerName As String, SlicerMsg As String)


 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 "Selection Limited to either OPEX or CAPEX, but not both." & vbCr _
      '  & "Please undo last selection."
      
      '--option b: warn user and undo
      MsgBox SlicerMsg
      With Application
         .EnableEvents = False
         .Undo
      End With
   End If
 End If
ExitProc:
   Application.EnableEvents = True
End Sub

Of course, there is another solution. You could use a combo box or data validation that allows only one selection, put a change sheet event on the cells and then use the value to update the pivot table filters.