View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.charting
aj
 
Posts: n/a
Default Help Programming an XY scatter Chart

I have a user form that inputs data onto a spreadsheet. I have a
command button that runs a macro that activates the sheet, selects the
data, and creates the XY Scatter chart. My Problem is that I don't know
how to set a loop to set each series' properties . (The number of
series changes every time) Bellow is an example where I manually set
the properties for three of the series. Any Ideas on how to create a
loop to set all.

Range("A:A,C:C,D:D").Select
Range("D1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("A1:A15,C1:D15"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C4"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C3"
ActiveChart.SeriesCollection(1).name = "=Sheet1!R2C1"
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R3C4"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R3C3"
ActiveChart.SeriesCollection(2).name = "=Sheet1!R3C1"
ActiveChart.SeriesCollection(3).XValues = "=Sheet1!R4C4"
ActiveChart.SeriesCollection(3).Values = "=Sheet1!R4C3"
ActiveChart.SeriesCollection(3).name = "=Sheet1!R4C1"
ActiveChart.Location Whe=xlLocationAsNewSheet, name:="xyCart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "risk"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Impact"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Probability"
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = False
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
End Sub