Hello everyone, (Excel 2003)

I have created a chart that plots seven series from data on a separate
worksheet. The data is plotted in XY (values against date & time). There
are also linear trendline associated with the plotted data. As such, the
need exists to plot data across over a small range of dates within the
overall dataset. I have done via a separate code which allows the user to
enter specific dates.

To make things easier for the user, I am writing macros to that allow the
user to scroll through a range of data (several days worth) by a single day
(Note: one day has multiple entries.

So, when the runs the macro, the entire chart steps forwards or backwards
one day in time.

Initially this was running off of the axis scale limits, however those can
be changed by the user and can lead to values outside the range of data (thus
unreliable to establish data ranges for the plot).

If the chart source data ranges are anywhere in the middle of the overall
dataset, the macros I have written works perfectly. The problem occurs when
I reach the first or last entry in the overall dataset.

Example: Say the overall dataset runs from 9/1/06 through today and the
overall dataset is in cells I3 through Q290. The chart source data for each
series references the same columns but rows 30 through 60, to view dates 9/5
- 9/15. When the user runs the macros, the idea is to read the current
series starting date from the minimum values in XValues. Then subtract one
to step back a day. Then scan the overall data set to determine the starting
and ending rows that cover dates 9/4-9/14. Then change the XValues and
Values for each series to plot the new data range.

The problem is when I get to top of the overall dataset. Say the user backs
up until the range 9/1 - 9/10 is plotted. Thus far everything worked fine.
Now the user wants to move forward to the range 9/2-9/11 and runs the macro.

At this point when the macro runs and bombs out with a 400 error upon the
first request for Seriescollection(1).XValues. I have stepped through the
code and established Watches for each of the series XValues.
Seriescollection(1).XValues shows <Unable to get the XValues property of the
series class, but all of the other XValues sets are present.

Why are the XValues not available? How is the 400 error (something about
forms) applicable when it uses no forms? Is this a known bug with a
workaround?

------------------------------------------
Sub slide_plot_range_left()

On Error GoTo Errorhandler

Application.ScreenUpdating = False

Dim first_date As Date
first_date = Application.WorksheetFunction.Min(Range("Date_Data "))
Dim last_date As Date
last_date = Application.WorksheetFunction.Max(Range("Date_Data "))

Charts("Glucose_Chart").Select

' Dim plot_start_date As Date
' plot_start_date = ActiveChart.Axes(xlCategory).MinimumScale - 1
' Dim plot_end_date As Date
' plot_end_date = ActiveChart.Axes(xlCategory).MaximumScale - 1

plot_start_date =
Application.WorksheetFunction.Min(SeriesCollection (1).XValues)
plot_start_date = Application.WorksheetFunction.Floor(plot_start_dat e,
1) - 1
plot_start_date = CDate(plot_start_date)

plot_end_date =
Application.WorksheetFunction.Max(SeriesCollection (1).XValues)
plot_end_date = Application.WorksheetFunction.Floor(plot_end_date, 1) - 1
plot_end_date = CDate(plot_end_date)

If plot_start_date < first_date Then GoTo Errorhandler

Do
i = i + 1
Loop Until Range("Date_Data").Cells(i, 1).Value = plot_start_date

j = i

Do
j = j + 1
Loop Until Range("Date_Data").Cells(j, 1).Value = plot_end_date

i = i + 2
j = j + 2

Charts("Glucose_Chart").SeriesCollection(1).XValue s =
Worksheets("Data").Range("I" + Format(i, "0") + ":I" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(1).Values =
Worksheets("Data").Range("J" + Format(i, "0") + ":J" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(2).XValue s =
Worksheets("Data").Range("I" + Format(i, "0") + ":I" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(2).Values =
Worksheets("Data").Range("M" + Format(i, "0") + ":M" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(3).XValue s =
Worksheets("Data").Range("I" + Format(i, "0") + ":I" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(3).Values =
Worksheets("Data").Range("L" + Format(i, "0") + ":L" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(4).XValue s =
Worksheets("Data").Range("I" + Format(i, "0") + ":I" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(4).Values =
Worksheets("Data").Range("O" + Format(i, "0") + ":O" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(5).XValue s =
Worksheets("Data").Range("I" + Format(i, "0") + ":I" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(5).Values =
Worksheets("Data").Range("N" + Format(i, "0") + ":N" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(6).XValue s =
Worksheets("Data").Range("I" + Format(i, "0") + ":I" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(6).Values =
Worksheets("Data").Range("Q" + Format(i, "0") + ":Q" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(7).XValue s =
Worksheets("Data").Range("I" + Format(i, "0") + ":I" + Format(j, "0"))
Charts("Glucose_Chart").SeriesCollection(7).Values =
Worksheets("Data").Range("P" + Format(i, "0") + ":P" + Format(j, "0"))

x_ll = ActiveChart.Axes(xlCategory).MinimumScale - 1
x_ul = ActiveChart.Axes(xlCategory).MaximumScale - 1

ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = x_ll
.MaximumScale = x_ul
.MinorUnit = 1
.MajorUnit = 2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.ChartArea.Select

Application.ScreenUpdating = True

Errorhandler:

Application.ScreenUpdating = True

End Sub