Unable to get the Values property of the Series Class

[Mike]

I have an XL97 macro that uses spreadsheet inputs to configure a pivot
table, and then subsequently update a chart. There are 3 date periods
(week, month, year) and a dropdown for Wctr (operation workcenter).
The macro works fine when I select week or month, but gives me the
Subject error msg when year (Case 3) is selected. The macro stops at
the *** line near the end of the code below. I added a "Watch" and
found that that series displayed "Unable to get the Values property of
the Series Class" as soon the line:

Sheets("Cht_TotalOrders").Select

was stepped thru. Any ideas as to what may be going on?

Many thanks in advance!

Mike

************************************
Application.ScreenUpdating = False

Dim ChtTtl As String
Dim DtRange As Range
Dim GoalRange As Range
Dim LblRange As Range
Dim MajUnitVal As Integer
Dim MaxScaleVal As Integer
Dim MyPvt As Object
Dim OlRange As Range
Dim OtRange As Range
Dim StrUserID As String
Dim TimePeriod As Integer
Dim TmPrd As String
Dim Wctr As String

'Get date range & workcenter...
TimePeriod = Range("TO_TimePeriod").Value '1, 2
or 3
Wctr = Left(Range("Wctr_TO").Value & " ", 10)
'Operation workcenter

'Select case...
Select Case TimePeriod
Case 1
'Week (7 days)
TmPrd = "ExpDt"
Set DtRange = Range("DtRange_Wk")
Set OtRange = Range("OtRange_Wk")
Set OlRange = Range("OlRange_Wk")
Set LblRange = Range("LblRange_Wk")
Set GoalRange = Range("GoalRange_Wk")
ChtTtl = Range("ChtTtlTO_Wk").Value
Case 2
'Month (5 weeks)
TmPrd = "Week"
Set DtRange = Range("DtRange_Mo")
Set OtRange = Range("OtRange_Mo")
Set OlRange = Range("OlRange_Mo")
Set LblRange = Range("LblRange_Mo")
Set GoalRange = Range("GoalRange_Mo")
ChtTtl = Range("ChtTtlTO_Mo").Value
Case 3
'Year (13 months)
TmPrd = "MoYr_Wk"
Set DtRange = Range("DtRange_Yr")
Set OtRange = Range("OtRange_Yr")
Set OlRange = Range("OlRange_Yr")
Set LblRange = Range("LblRange_Yr")
Set GoalRange = Range("GoalRange_Yr")
ChtTtl = Range("ChtTtlTO_Yr").Value
Case Else
MsgBox TimePeriod & " is not a valid time period!"
Exit Sub
End Select

'Configure pivot table to selected time period and workcenter...
Sheets("Pvt_TO").Select
Set MyPvt = ActiveSheet.PivotTables("pvtTotalOrders")
MyPvt.PivotSelect "Wctr", xlButton
MyPvt.AddFields RowFields:=TmPrd, _
ColumnFields:="Data", PageFields:="Wctr"
MyPvt.PivotSelect TmPrd, xlButton
MyPvt.PivotFields(TmPrd).PivotItems("(blank)").Visible = False
MyPvt.PivotFields("Wctr").CurrentPage = Wctr

'Copy chart data...
Range("CD_TO2").Value = Range("CD_TO1").Value

'Update chart per time period and workcenter...
Sheets("Cht_TotalOrders").Select
'ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = DtRange
ActiveChart.SeriesCollection(1).Values = OtRange
ActiveChart.SeriesCollection(2).Values = OlRange
***ActiveChart.SeriesCollection(3).Values = LblRange
ActiveChart.SeriesCollection(4).Values = GoalRange

'Update chart title...
ActiveChart.ChartTitle.Select
Selection.Characters.Text = ChtTtl
ActiveWindow.Visible = False

Application.ScreenUpdating = True

 

[Mike]

Hi All,

I've found that Microsoft Knowledge Base Article 139327 (Error
Programmatically Accessing Data Series on Chart) provided the answer
to my problem. It seems that an intermediate step in my program logic
caused the series in question to become "invisible". There is
apparently an Excel bug which doesn't allow an invisible series to be
changed "programmatically". The solution was a minor code change to
prevent the series values from being set to "".

Mike