Timeline Slicer Excel 2013 VBA

sperando che qualcuno possa aiutare

Sto cercando di call un valore di affettatore timeline in vba in modo da poter controllare altri affettatrici. L'ho gestito dalle cellule, ma mi chiedevo se possa essere controllato dal mio affettatore master

Sub Slicer_Time_Change() Dim startDate As Date, endDate As Date startDate = Range("A1") 'Set slicer 1 start date as slicer selection instead endDate = Range("B1") 'Set slicer 1 End date as slicer selection instead ActiveWorkbook.SlicerCaches("NativeTimeline_Date1").TimelineState. _ SetFilterDateRange startDate, endDate ActiveWorkbook.SlicerCaches )("NativeTimeline_Date2").TimelineState. _ SetFilterDateRange startDate, endDate 

End Sub

Molte grazie in anticipo!

Andando a rispondere a me stesso!

 Sub Slicer_Time_Change() 'Gets data from master slicer selection Set cache = ActiveWorkbook.SlicerCaches("NativeTimeline_Date1") 'Puts into cell Cells(1, 1) = cache.TimelineState.startDate Cells(1, 2) = cache.TimelineState.endDate Dim startDate As Date, endDate As Date startDate = Range("A1") endDate = Range("B1") 'Takes data from cell and controls other slicers with date range ActiveWorkbook.SlicerCaches("NativeTimeline_Date2").TimelineState. _ SetFilterDateRange startDate, endDate End Sub 

Ho avuto lo stesso problema. La tua risposta ha aiutato, ma ho avuto bisogno di questo innescato quando l'affettatrice master cambiato. Purtroppo le affettatrici non hanno events, ma le tabelle pivot di cui hanno effetto. Il codice riportto di seguito aggiorna tutti gli altri affettatrici della timeline quando il taglieratore master cambia, che a sua volta modifica la sua tabella pivot, che può essere utilizzata per triggersre le modifiche a tutti gli altri affettatrici.

 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 ThisWorkbook.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 Private Sub Workbook_SheetPivotTableUpdate (ByVal Sh come object, Target ByVal come tabella pivot) 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 ThisWorkbook.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 Errore ErrHandler Errore Errore 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 ThisWorkbook.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 Application.EnableEvents = Falso 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 ThisWorkbook.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 Fine con 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 ThisWorkbook.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 Seleziona Err.Number caso 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 ThisWorkbook.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 Caso Else: 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 ThisWorkbook.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 Fine Seleziona 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 ThisWorkbook.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 Fine Seleziona 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 ThisWorkbook.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