One Timeline to Rule Them All
Post date: Oct 19, 2016 6:21:44 PM
My dashboard has several charts. They come from different sources. Their data is from the same date range. I want one timeline slicer to control them all.
Timeline slicers can be connected to several Pivots and Pivot Charts so long as the Pivots all use the same data. If they don't we must use separate timelines for each pivot source. Even so, with a little VBA, and leveraging a small trick we can control the other slicers and, thus, the other charts from the first slicer we add to our dashboard.
Slicers do not have events.So we need a 'trick' to update other slicers from the first. The trick is when a slicer changes it changes the pivot it is connected to and pivot updates are monitored at the worksheet and workbook level. By placing this code in our ThisWorkbook module we can achieve our goal of one timeline to rule them all.
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) ' Description:Update Timeline Slicers from Master Slicer ' Inputs: Sh PivotTable's worksheet ' Target PivotTable being changed/updated ' Outputs: *None ' Requisites: *None ' Example: *None - This is an event handler found in ThisWorkbook module ' Date Ini Modification ' 10/19/16 CWH Initial Development ' Declarations Const cRoutine As String = "Workbook_SheetPivotTableUpdate" Dim oSlicer As SlicerCache 'Current Slicer Const cSlicer As Long = 1 'Master Slicer Dim dStartDate As Date 'Start Date Dim dEndDate As Date 'End Date Dim bCleared As Boolean 'Filter Cleared Flag Dim bEvents As Boolean 'Events Enabled Flag ' Error Handling Initialization On Error GoTo ErrHandler ' Prevent cascading events bEvents = Application.EnableEvents Application.EnableEvents = False ' Get Master Slicer's dates Set oSlicer = ThisWorkbook.SlicerCaches(cSlicer) bCleared = oSlicer.FilterCleared If Not bCleared Then With oSlicer.TimelineState dStartDate = .FilterValue1 dEndDate = .FilterValue2 End With End If ' Set All other Timeline Slicer Dates For Each oSlicer In oWkb.SlicerCaches If oSlicer.SlicerCacheType = xlTimeline And _ oSlicer.Index <> cSlicer Then If bCleared Then _ oSlicer.ClearAllFilters Else _ oSlicer.TimelineState.SetFilterDateRange _ StartDate:=dStartDate, EndDate:=dEndDate End If Next ErrHandler: Select Case Err.Number Case Is = 0: 'Do nothing Case Is = 9: 'Do Nothing Master Slicer Missing Case Else: Select Case MsgBox(Prompt:=Err.Description, _ Buttons:=vbAbortRetryIgnore, _ Title:=cRoutine, _ HelpFile:=Err.HelpFile, _ Context:=Err.HelpContext) Case Is = vbAbort: Stop: Resume 'Debug mode - Trace Case Is = vbRetry: Resume 'Try again Case Is = vbIgnore: 'End routine End Select End Select ' Clean up: Resume responding to events Application.EnableEvents = bEvents End Sub