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
Bookmarks