[Jon Peltier[_3_]]

Ant -

Check out

xlsChart.Parent.BottomRightCell

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Ant wrote:
Hi All,

I'm creating many worsheets all with 15-20 charts of type
xl3DColumnClustered. I need to plot the source data of each chart,
below each of these charts. Hence I need to know the row at which a
chart ends.

Can someone help me out, to find out the row where the chart ends. I
use a procedure to add the chart which is as follows.

While creating the charts, each chart is positioned based of the
argument chtTop which I increment by 400 for each call of this
procedure. Whats is this 400 actually, from this can we find out where
the chart ends or where the next chart begins?


Public Sub addChart(strChartXVal As String, strChartYVal As String,
ByVal strChartTitle As String, strChartXTitle As String,
strChartYTitle As String, ByVal strSheet As String, ByVal chtTop As
Long, chtNo As Integer, Optional strChartY1Val As String, Optional
strLabelYVal As String, Optional strLabelY1Val As String)
With xlsapp.Application
Dim xlsChart As Excel.Chart
.Sheets(strSheet).Select
Set xlsChart = .ActiveSheet.ChartObjects.Add(0, chtTop, 450,
250).Chart
xlsChart.ChartType = xl3DColumnClustered 'xlColumnClustered
If chtNo = 1 Then
xlsChart.SeriesCollection.NewSeries
xlsChart.SeriesCollection(1).XValues = "=(" & strChartXVal
& ")"
xlsChart.SeriesCollection(1).Values = "=(" & strChartYVal
& ")"
xlsChart.HasLegend = False
xlsChart.ApplyDataLabels 2, False
xlsChart.SeriesCollection(1).DataLabels.Font.Name =
"Arial"
xlsChart.SeriesCollection(1).DataLabels.Font.FontS tyle =
"Regular"
'040703
xlsChart.SeriesCollection(1).DataLabels.Font.Size = 8
xlsChart.SeriesCollection(1).DataLabels.Font.Color Index =
2
xlsChart.SeriesCollection(1).DataLabels.Font.Backg round =
2 ' xlTransparent
'030703
If strChartY1Val = "D" Then
xlsChart.SeriesCollection(1).Points(xlsChart.Serie sCollection(1).Points.Count
- 1).Interior.ColorIndex = 36
xlsChart.SeriesCollection(1).Points(xlsChart.Serie sCollection(1).Points.Count).Interior.ColorIndex
= 6
End If
ElseIf chtNo = 2 Then
xlsChart.SeriesCollection.NewSeries
xlsChart.SeriesCollection.NewSeries
xlsChart.SeriesCollection(1).XValues = "=(" & strChartXVal
& ")"
xlsChart.SeriesCollection(1).Values = "=(" & strChartYVal
& ")"
xlsChart.SeriesCollection(1).Name = "=" & strLabelYVal

xlsChart.ApplyDataLabels 2, False
xlsChart.SeriesCollection(1).DataLabels.Font.Name =
"Arial"
xlsChart.SeriesCollection(1).DataLabels.Font.FontS tyle =
"Regular"
'040703
xlsChart.SeriesCollection(1).DataLabels.Font.Size = 8
xlsChart.SeriesCollection(1).DataLabels.Font.Color Index =
2
xlsChart.SeriesCollection(1).DataLabels.Font.Backg round =
2 ' xlTransparent
xlsChart.SeriesCollection(2).Values = "=(" & strChartY1Val
& ")"
xlsChart.SeriesCollection(2).Name = "=" & strLabelY1Val
xlsChart.SeriesCollection(2).DataLabels.Font.Name =
"Arial"
xlsChart.SeriesCollection(2).DataLabels.Font.FontS tyle =
"Regular"
'040703
xlsChart.SeriesCollection(2).DataLabels.Font.Size = 8
xlsChart.SeriesCollection(2).DataLabels.Font.Color Index =
2
xlsChart.SeriesCollection(2).DataLabels.Font.Backg round =
2 ' xlTransparent
xlsChart.HasLegend = True
xlsChart.Legend.Position = -4107 'xlLegendPositionBottom
End If

With xlsChart
.HasTitle = True
.ChartTitle.Characters.Text = strChartTitle
.Axes(1).HasTitle = True
.Axes(1).AxisTitle.Characters.Text = strChartXTitle
.Axes(1).TickLabels.Font.Name = "Arial"
.Axes(1).TickLabels.Font.FontStyle = "Regular"
.Axes(1).TickLabels.Font.Size = 8
.Axes(1).TickLabels.Alignment = -4108 'xlCenter
.Axes(1).TickLabels.Orientation = -4171 'xlUpward
.Axes(1).HasMajorGridlines = False
.Axes(1).HasMinorGridlines = False
'040703
.Axes(1).TickLabelSpacing = 1
.Axes(1).TickMarkSpacing = 1

.Axes(2).HasTitle = True
.Axes(2).AxisTitle.Characters.Text = strChartYTitle
.Axes(2).AxisTitle.Font.Name = "Arial"
.Axes(2).AxisTitle.Font.FontStyle = "Regular"
.Axes(2).AxisTitle.Font.Size = 7
.Axes(2).AxisTitle.HorizontalAlignment = -4108 'xlCenter
.Axes(2).AxisTitle.VerticalAlignment = -4108 'xlCenter
.Axes(2).AxisTitle.Orientation = -4128 'xlHorizontal
.Axes(2).AxisTitle.Left = 45
.Axes(2).MinimumScale = 0
.Axes(2).MaximumScale = 10
.Axes(2).MinorUnit = 1.666666666
.Axes(2).MajorUnit = 5
.Axes(2).Crosses = -4105 'xlAutomatic
.Axes(2).ReversePlotOrder = False
.Axes(2).ScaleType = -4132 'xlLinear
.Axes(2).DisplayUnit = -4142 'xlNone
.Axes(2).HasMajorGridlines = True
.Axes(2).HasMinorGridlines = True
'''''''''''''''''''''''''''
'040703
.Elevation = 15
.Perspective = 30
.Rotation = 20
.RightAngleAxes = True
.HeightPercent = 100
.AutoScaling = True
.PlotArea.Top = 22
.PlotArea.Width = .ChartArea.Width
.PlotArea.Height = .ChartArea.Height - 45
End With
End With
End Sub


Thanks in advance.

Ant