VBA To Auto-Clear one slicer based on the selection of another slicer.

[DavidT77]

Is it possible to write code that clears one slicer based on the selection of another slicer? I have a Category and a Region slicer connected to several charts on a dashboard (in addition to several other slicers too). If I select Category 6 in the Category slicer, I want Excel to auto-clear the Region slicer so that it automatically shows all 4 Regions. Yea, even though it doesn't take much effort to click the red X on the Region slicer to clear it, it gets overlooked too easily by those consuming this dashboard. Because of that, I want my dashboard to automatically "Clear Filter" for the Region slicer but only when/if Category 6 is selected. I'm still new-ish to VBA so any help is much appreciated!

 

[John_w]

This isn't easy because no events are triggered when the user selects a slicer item, nor when the filtered rows change. One workaround is to use an Application.OnTime timer to see if the Category 6 item in the Category slicer is selected, however I'm not sure how well this code will work for you. Application.OnTime has a minimum interval of 1 second, so the user might see a slight lag between clicking Category 6 and the code clearing the Region slicer.

Put this code in ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

Put this code in a new standard module:

Option Explicit

Public RunWhen As Double
Public Const cRunWhat = "Clear_Region_Slicer"

Public Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, 1)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub

Public Sub Clear_Region_Slicer()
    With ThisWorkbook
        If .SlicerCaches("Slicer_Category").SlicerItems("Category 6").Selected Then
            .SlicerCaches("Slicer_Region").ClearManualFilter
        End If
    End With
    StartTimer  'Restart the timer
End Sub

Public Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False
End Sub

Save, close and reopen the workbook (.xlsm or .xlsb) to test the code.

 

[DavidT77]

Wow, so this comes extremely close to working. It actually does work by resetting the Region slicer whenever Category 6 is selected so that in itself is impressive. The issue now, though, is that it runs the macro every second when it really only needs to run it once when Cat 6 is first selected.

Is there a way to extend the timer time to say, 1, 5, 10, or even 20 mins but have it reset to 0 whenever Category 6 is selected in the Slicer? For what it's worth, these slicers and charts are connected to a Pivot Table on another sheet. I've been trying to work something akin to "If cell B2 on Sheet 2 = "Category 6" Then Clear Region_Slicer (or set Region = ALL)." Cell B2 on Sheet2 is the Category filter on the pivot table that controls the charts. There's another filter in B3 that selects for Region. That's the logic anyways.

 

[John_w]

See if this works better - it replaces all the code in the standard module.

Option Explicit

Public RunWhen As Double
Public Const cRunWhat = "Clear_Region_Slicer"
Public Category6Selected As Boolean

Public Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, 1)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub

Public Sub Clear_Region_Slicer()
    Static init As Boolean
    With ThisWorkbook
        If Not init Then
            Category6Selected = .SlicerCaches("Slicer_Category").SlicerItems("Category 6").Selected
            init = True
        End If
        If Not Category6Selected Then
            If .SlicerCaches("Slicer_Category").SlicerItems("Category 6").Selected Then
                .SlicerCaches("Slicer_Region").ClearManualFilter
                Category6Selected = True
            End If
        Else
            If Not .SlicerCaches("Slicer_Category").SlicerItems("Category 6").Selected Then
                Category6Selected = False
            End If
        End If
    End With
    StartTimer  'Restart the timer
End Sub

Public Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False
End Sub

Your If statement would be:

    If Worksheets("Sheet2").Range("B2").Value = "Category 6" Then ThisWorkbook.SlicerCaches("Slicer_Region").ClearManualFilter