Your description sounds like one of the column series is on the opposite
axis from the other two. Instead of using this kind of chart type:
..ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"
just make a regular column (or line) chart, then series by series, change
half to the other type, and if needed, put some onto the secondary axis.
This gives you better control over how each series is displayed.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"(PeteCresswell)" wrote in message
...
I've got a chart with three lines and three sets of bars on it.
viz: http://tinyurl.com/3cjjbg
Two of the bars behave as expected, being side-by-side.
The third set of bars, however, seems to sit on top of the other two
instead of
next to them.
Can anybody offer up a SeriesCollection(n).Property that I could set to
coerce
that one set to be side-by-side like the others?
For whatever it's worth (probably not much...),
here's the code that creates the chart:
================================================== =============================
Private Sub chart_Create(ByRef theCI() As mChartInfo, ByRef theWS As
Excel.Worksheet)
4000 debugStackPush mModuleName & ": chart_Create"
4001 On Error GoTo chart_Create_err
' PURPOSE: To create a single chart on our spreadsheet
' ACCEPTS: - Pointer to array of struct containing info about the chart
we
will create
' - Pointer to the worksheet we will put the chart on
4002 Dim mySS As Excel.Application
Dim myChart As Excel.Chart
Dim mySeries_Bar() As Excel.Series
Dim mySeries_Line() As Excel.Series
Dim i As Long
Dim maxRows As Long
Dim trancheCount As Long
Dim myTitle As String
Const myTickLabelFontSize As Long = 8
' -----------------------------------------------------------
' Create chart
4030 Set mySS = theWS.Parent.Parent
4039 Set myChart = mySS.Charts.Add
' --------------------------------------------------------
' Sometimes, for unknown reasons, there may already be one or more
' series. We want to start with a clean slate, so we delete them
' before creating our own
4080 With myChart
4040 Do Until .SeriesCollection.Count = 0
4041 .SeriesCollection(1).Delete
4042 Loop
'4043 Set mySeries_Bar = .SeriesCollection.NewSeries
'4044 Set mySeries_Line = .SeriesCollection.NewSeries
4049 End With
' --------------------------------------------------------
' Figure out how many tranches we have
4050 trancheCount = UBound(theCI)
' --------------------------------------------------------
' Determine the max number of rows (i.e. payments)
' of all the tranches.
' "RowCount" is sitting at the number of the last
' row written for that tranche's section of the data grid
4060 For i = 0 To trancheCount - 1
4061 If mTI(i).RowCount maxRows Then
4062 maxRows = mTI(i).RowCount
4063 End If
4069 Next i
' --------------------------------------------------------
' Again, looping through the array,
' Add our own series and point it to our data
4100 For i = 0 To trancheCount - 1
4101 ReDim Preserve mySeries_Bar(i)
4109 Set mySeries_Bar(i) = myChart.SeriesCollection.NewSeries
4110 With mySeries_Bar(i)
4113 .ChartType = xlColumnClustered
4111 .XValues = "='" & theWS.Name & "'!" &
theCI(i).RangeAddress_XLabels
4112 .Name = theCI(i).CellAddress_Bar_Name
4114 .Values = "='" & theWS.Name & "'!" &
theCI(i).RangeAddress_Bar_Values
4119 End With
4120 ReDim Preserve mySeries_Line(i)
4129 Set mySeries_Line(i) = myChart.SeriesCollection.NewSeries
4130 With mySeries_Line(i)
4133 .ChartType = xlColumnClustered 'xlLineMarkers
4131 .XValues = "='" & theWS.Name & "'!" &
theCI(i).RangeAddress_XLabels
4132 .Name = theCI(i).CellAddress_Line_Name
4134 .Values = "='" & theWS.Name & "'!" &
theCI(i).RangeAddress_Line_Values
4139 End With
4199 Next i
' --------------------------------------------------------
' Create an invisible text box that we use to pass on the rowcount
' to charts_Arrange - which needs to grow the width of the chart
depending on
' now many payments need to be shown without the labels piling up
4210 With myChart
4211 .Shapes.AddTextbox(gExcelConstant_TextOrientation_ Horizontal, 0,
0, 100,
15).Name = mLit_PaymentCount
4212 With .Shapes(mLit_PaymentCount)
4213 .TextFrame.Characters.Text = maxRows
4214 .Visible = False
4215 End With
4219 End With
' --------------------------------------------------------
' Coerce the chart type to a custom format
' NB: For some reason, this and the following prop settings
' work only AFTER we have set up the series...
4310 With myChart
4319 .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on
2
Axes"
4320 .HasLegend = True
4321 With .Legend
'4212 .Width = theWS.ChartObjects(1).Width
4323 .Position = xlBottom
4324 .Border.LineStyle = xlNone
4329 End With
4330 .HasTitle = True
4339 .ChartTitle.Characters.Text = theCI(0).Title
4340 .Axes(xlValue, xlPrimary).HasTitle = True
4341 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
mLit_AxisTitle_PctNotional 'Looks like this one is the bars
4342 .Axes(xlValue, xlSecondary).HasTitle = True
4343 .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
mLit_AxisTitle_CumNotional 'Looks like this one is the lines
4344 .Axes(xlCategory).HasTitle = True
4345 .Axes(xlCategory).AxisTitle.Characters.Text =
mLit_AxistTitle_PaymentNum
'This is the value stream along the bottom: the x-axis labels?
4349 .ChartGroups(1).GapWidth = 300 'Make bars thinner
4399 End With
4400 For i = 0 To trancheCount - 1
4409 Next i
' --------------------------------------------------------
' Coerce the chart's subtypes to line/bar
' Again, the order in which this is done counts: it does not
' work until we have done the .ApplyCustomType
4410 For i = 0 To trancheCount - 1
4420 With mySeries_Bar(i)
4421 .ChartType = xlColumnClustered
4422 .Border.ColorIndex = mTI(i).BarColor_Outline
4423 .Interior.ColorIndex = mTI(i).BarColor_Body
4429 End With
4430 With mySeries_Line(i)
4431 .ChartType = xlLineMarkers
4432 .MarkerBackgroundColorIndex = mTI(i).LineMarkerColor_Body
4433 .MarkerForegroundColorIndex = mTI(i).LineMarkerColor_Outline
4434 .MarkerStyle = mTI(i).LineMarkerShape
4435 .Smooth = True
4436 .MarkerSize = 7
4439 .Shadow = False
4440 With .Border
4441 .ColorIndex = mTI(i).LineColor_Line
4442 .Weight = xlMedium
4443 .LineStyle = xlContinuous
4449 End With
4450 End With
4499 Next i
4998 myChart.Location Whe=xlLocationAsObject, Name:=theWS.Name
chart_Create_xit:
DebugStackPop
On Error Resume Next
For i = 0 To trancheCount - 1
Set mySeries_Bar(i) = Nothing
Set mySeries_Line(i) = Nothing
Next i
Set myChart = Nothing
Exit Sub
chart_Create_err:
' With theCTFI_Array(theEntityTypeID, theFundID)
' BugAlert True, "ChartTitle='" & theChartTitle & "',
ValueColumnOffset='" &
theDataRangeValueColumnOffset & "', FundName='" & .FundName & "',
DataSheet='" &
.DataSheetName & "', DataRange='" & .DataRangeAddress & "'."
' End With
BugAlert True, "i='" & i & "'."
Resume chart_Create_xit
End Sub
================================================== ==================================
--
PeteCresswell