Charts switch from 'Series in Rows' to 'Series in Columns'

  • Thread starter Thread starter Peace
  • Start date Start date
P

Peace

I use VBA to create charts in Excel 2003, but find that sometimes the
Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not
intended), even if I have specified 'Series in Rows'. This happens
intermittently, and I am not sure what I am doing wrong. I do save
the workbook as Microsoft Excel 97 so that a user with Excel 2000 or
Excel 2003 can use the workbook.

Thank you for any suggestions.
 
Hello Jon, Appreciate your response. I wondered if anyone had had a
similar issue for which a solution already exists. I did not post the
code as it is very lengthy, and in different modules. I am hopefully
reproducing the relevant portion of it. I would be happy to share the
workbook if that would help. Thanks.

The code is designed to chart data by column, or by row, depending on
the position of the active cell in the table underlying the chart. If
the activecell is other than row 15, and is in column 1, then the row
provides data points for the chart. If the active cell is in row 15
and other than column 1, then that column provides the data points.
If the active cell is elsewhere, it is forced to 'come' to row 15,
column 1 till the user makes a selection.

Sub UpdateChart()

On Error Resume Next
Set TheChartObj = ActiveSheet.ChartObjects(1)
Set TheChart = TheChartObj.Chart
UserRow = ActiveCell.Row
UserCol = ActiveCell.Column

krows = _
Application.WorksheetFunction.CountA(ActiveSheet. _
Range("A:A").SpecialCells(xlCellTypeVisible))
kcols = _
Application.WorksheetFunction.CountA(ActiveSheet. _
Rows("15:15").SpecialCells(xlCellTypeVisible))

'======================================================================
'clear shading of rows
Range(Cells(16, 2), Cells(100, 100)).Interior.ColorIndex = xlNone
'======================================================================

'code to shift from row to columns
If UserCol > 1 And UserCol < kcols + 1 And UserRow = 15 Then
'for vertical
Set SrcRange = ActiveSheet.Range(Cells(15, UserCol), Cells(15 +
krows - 1, UserCol))
PlotBy = xlColumns
ElseIf UserRow > 15 And UserRow < (15 + krows) And UserCol = 1 Then
'for horizontal
Set SrcRange = ActiveSheet.Range(Cells(UserRow, StartCol),
Cells(UserRow, EndCol))
Set SrcRange1 = ActiveSheet.Range(Cells(25, StartCol), Cells(25,
StartCol + EndCol))
PlotBy = xlRows
Else
Range("A15").Select
PlotBy = 0
End If
'======================================================
Select Case PlotBy

Case 1 ' by xlColumns
SrcRange.Interior.ColorIndex = 7
TheChart.SeriesCollection(1).Name = "=" & ActiveSheet.Name & "!R" &
UserRow & "C1"
TheChart.SetSourceData Source:=SrcRange, PlotBy:=PlotBy
TheChart.SeriesCollection.NewSeries
TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

'set label for each series
Set xlblrng = ActiveSheet.Range(Cells(15, StartCol), Cells(15,
StartCol + EndCol - 2))
For Each srs In TheChart.SeriesCollection
srs.XValues = xlblrng
Next
TheChart.HasTitle = True
TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B77") &
vbCr & ActiveSheet.Range("B78")
'----------------------------------------
Case 2 ' by xlrows
SrcRange.Interior.ColorIndex = 4
TheChart.SetSourceData Source:=SrcRange, PlotBy:=PlotBy

'set label for each series
Set xlblrng = ActiveSheet.Range(Cells(16, 1), Cells(16 + krows, 1))
For Each srs In TheChart.SeriesCollection
On Error Resume Next
srs.XValues = xlblrng
Next
TheChart.HasTitle = True
TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B75") &
vbCr & ActiveSheet.Range("B76")

End Select
'======================================================
Select Case ActiveSheet.Name
Case Is = "Region_Year_o_Year"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 8)).Interior.ColorIndex = 15
ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlYears
kMinorUnitScale = xlYears
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlYears
kMinorUnitScale = xlYears
ChartTitleText = "HPA Cross-Section for Year " &
Format(Cells(15, UserCol).Value, "yyyy")
End If

TheChart.HasTitle = True
TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B75") &
vbCr & ActiveSheet.Range("B76")

Case Is = "State_Year_o_Year"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
& StartCol & ":R67C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 8)).Interior.ColorIndex = 15
ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlYears
kMinorUnitScale = xlYears
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlYears
kMinorUnitScale = xlYears
ChartTitleText = "HPA Cross-Section for Year " _
& Format(Cells(15, UserCol).Value, "yyyy")
End If

Case Is = "Region_Qrtr_o_Qrtr"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 29)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 3
kMinorUnit = 3
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 3
kMinorUnit = 3
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Cross-Section for Quarter ending " _
& Format(Cells(15, UserCol).Value, "mmm-yyyy")
End If

Case Is = "State_Qrtr_o_Qrtr"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
& StartCol & ":R67C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 29)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 3
kMinorUnit = 3
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 3
kMinorUnit = 3
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Cross-Section for Quarter ending " _
& Format(Cells(15, UserCol).Value, "mmm-yyyy")

End If

Case Is = "Region_Month_o_Month"
TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 85)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Cross-Section for Month " _
& Format(Cells(15, UserCol).Value, "mmm-yyyy")
End If

Case Is = "State_Month_o_Month"
TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
& StartCol & ":R67C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 85)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Cross-Section for Month " _
& Format(Cells(15, UserCol).Value, "mmm-yyyy")
End If
End Select

'==========================================
Select Case PlotBy

Case 1 ' by xlColumns, timeseries, horizontal
With ActiveSheet.ChartObjects
' .Top = Range(Cells(2, StartCol), Cells(2, StartCol)).Top
.Left = Range(Cells(2, StartCol), Cells(2, StartCol)).Left
End With
ActiveWindow.ScrollColumn = StartCol

Case 2 ' by xlrows = vertical
With ActiveSheet.ChartObjects
' .Top = Range(Cells(2, ActiveCell.Column), Cells(2,
ActiveCell.Column)).Top
.Left = Range(Cells(15, ActiveCell.Column), Cells(15,
ActiveCell.Column)).Left
End With
ActiveWindow.ScrollColumn = ActiveCell.Column
End Select

With TheChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = kMajorUnit
.MajorUnitScale = kMajorUnitScale
.MinorUnit = kMinorUnit
.MinorUnitScale = kMinorUnitScale
.Crosses = xlCustom
.AxisBetweenCategories = False
.ReversePlotOrder = False

.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
TheChart.ChartTitle.Text = ChartTitleText
TheChart.Activate
ActiveChart.Axes(xlCategory).Select
'With Selection.TickLabels
'.Alignment = xlCenter
'.Offset = 100
'.ReadingOrder = xlContext
'.Orientation = 90
'End With

'TheChartObj.Chart.ChartType = 51
TheChart.SeriesCollection(1).Name = "=" & ActiveSheet.Name & "!R" &
UserRow & "C1"
TheChart.Refresh
TheChart.Axes(xlCategory).TickLabelPosition = xlLow
TheChartObj.Visible = True
Application.ScreenUpdating = True
End Sub
 
What are typical values for krows and kcols?

At first glance I notice that in the Select Case PlotBy, under Case
xlColumns, you have values defined by

"=" & ActiveSheet.Name & "!R25C" & StartCol & ":R25C" & StartCol +
EndCol - 2

which means it is a range one row high by several columns wide (the same
type of values definition appears in several places in the worksheet name
select case). This is the definition of "by row", isn't it?

You should do the SetSourceData before defining SeriesCollection(1).Name.

- Jon
 
Thanks, Jon, for your suggestions.
krows take on values from 9 to 52.
kcols take on values from 1 to 84

I will check out your suggestion to SetSourceData before defining
SeriesCollection(1).Name and also examine the code for possible
errors.

Have a good day.
 
Back
Top