Scrolling Through Time Series

Create/add a time series that covers 4 months (about 103 days)
Add an ActiveX Scrollbar control to the worksheet
SS
Change the Max property value to the number of days
In this example, change it to 103
Change the name to ScrollBarTimeSeries
Double click the scrollbar and add the following line of code:


Private Sub ScrollBarTimeSeries_Change() 
   Range("F4").Value = Me.ScrollBarTimeSeries.Value
End Sub

This will mean that when the scrollbar is adjusted the corresponding value will appear in cell "F4".


Exit Design mode and adjust the scrollbar
SS


The value in cell "F4" can then be used to provide the necessary row offset for the chart source data.
Create the following Workbook named ranges:
HorizontalAxis - OFFSET(Sheet1!$B$2,0,0,Sheet1!$F$4,)
Series1Values - OFFSET(Sheet1!$C$2,0,0,Sheet1!$F$4,)
Series2Values - OFFSET(Sheet1!$D$2,0,0,Sheet1!$F$4,)


Select Range "B2:D20" and create a column chart


Select "Series1" on the chart and change the series formula:
Series(,Book1!HorizontalAxis,Book1!Series1Values,1)


Select "Series2" on the chart and change the series formula:
Series(,Book1!HorizontalAxis,Book1!Series2Values,1)


Adjust the scrollbar



© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext