Home Menu

Menu


ExcelBanter » Excel Newsgroups » Charts and Charting in Excel > Charts switch from 'Series in Rows' to 'Series in Columns'
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 2
Default Charts switch from 'Series in Rows' to 'Series in Columns'
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.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 6,582
Default Charts switch from 'Series in Rows' to 'Series in Columns'
Hard to tell if you keep the code secret.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peace" wrote in message
oups.com...
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.



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 2
Default Charts switch from 'Series in Rows' to 'Series in Columns'
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


On Mar 21, 4:01 pm, "Jon Peltier"
wrote:
Hard to tell if you keep the code secret.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Peace" wrote in message

oups.com...



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.- Hide quoted text -

- Show quoted text -


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 6,582
Default Charts switch from 'Series in Rows' to 'Series in Columns'
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peace" wrote in message
ups.com...
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


On Mar 21, 4:01 pm, "Jon Peltier"
wrote:
Hard to tell if you keep the code secret.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Peace" wrote in message

oups.com...



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.- Hide quoted text -

- Show quoted text -




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 18
Default Charts switch from 'Series in Rows' to 'Series in Columns'
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.



On Mar 21, 7:50 pm, "Jon Peltier"
wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Peace" wrote in message

ups.com...



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 &

...

read more »- Hide quoted text -

- Show quoted text -


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i move data series into other columns on column charts JamesO Charts and Charting in Excel 1 August 3rd 06 05:42 PM
chart data series -- plot a table as a single series hjc Charts and Charting in Excel 7 September 20th 05 05:52 PM
how do I change a line series to a column series in excel? Mati Charts and Charting in Excel 1 May 12th 05 09:32 AM
Different width of columns (series) in clustered charts Bamboozled Charts and Charting in Excel 2 April 12th 05 01:00 PM
series graph -- one series being added to another series rich zielinski via OfficeKB.com Charts and Charting in Excel 3 March 30th 05 06:23 PM


All times are GMT +1. The time now is 12:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement