ManUBlueJay
Active Member
- Joined
- Aug 30, 2012
- Messages
- 326
- Office Version
- 365
- Platform
- Windows
I have 2 slicers running a pivot table on a sheet called "Entry Form"
Slicer 1 is controlling a named cell called "CEF_Entry"
Slicer 2 is controlling a named range called "CEF_Type"
I would like it that when I change slicer 1 it clears Slicer 2' selection and locks my entry form requiring a selection from Slicer 2. My code does never reach the slicer 2 change
When Slicer 2 is used it unlocks my entry form.
I have used the following code in the relevant sheet code
Slicer 1 is controlling a named cell called "CEF_Entry"
Slicer 2 is controlling a named range called "CEF_Type"
I would like it that when I change slicer 1 it clears Slicer 2' selection and locks my entry form requiring a selection from Slicer 2. My code does never reach the slicer 2 change
When Slicer 2 is used it unlocks my entry form.
I have used the following code in the relevant sheet code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("CEF_Entry")) Is Nothing And Range("CEF_Type") = "(All)" Then
Exit Sub
Else
If Not Intersect(Target, Range("CEF_Type")) Is Nothing Then
Application.EnableEvents = False
CompostEntryForm
Application.EnableEvents = True
Else
If Not Intersect(Target, Range("CEF_Entry")) Is Nothing And Range("CEF_Type") <> "(All)" Then
Application.EnableEvents = False
Sheets("Entry Form").Unprotect
ActiveWorkbook.SlicerCaches("Slicer_Type").ClearManualFilter
Range("CompostEntryForm").Locked = True
Sheets("Entry Form").Protect AllowUsingPivotTables:=True, UserInterFaceOnly:=True
Application.EnableEvents = True
End If
End If
End If
End Sub