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

This procedure does what you asked. Note that there are still a few
redundant and unneeded steps (the chart recorder records even default
settings).

Sub DoTheChart()
Dim WS As Worksheet
Dim Cht As Chart
Dim Rng As Range
Dim iRow As Long

Set WS = ActiveSheet
Set Cht = Charts.Add
Cht.ChartType = xlXYScatter

Set Rng = WS.Range(WS.Range("A2"), WS.Range("A2").End(xlDown))
For iRow = 2 To 1 + Rng.Rows.Count
Cht.SeriesCollection.NewSeries
With Cht.SeriesCollection(iRow - 1)
.XValues = "='" & WS.Name & "'!R" & iRow & "C4"
.Values = "='" & WS.Name & "'!R" & iRow & "C3"
.Name = "='" & WS.Name & "'!R" & iRow & "C1"
End With
Next
With Cht
.HasTitle = True
.ChartTitle.Text = "risk"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Impact"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Probability"
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = False
End With
With Cht.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With Cht.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
Cht.HasLegend = False
End Sub

See this page for more about charts and VBA:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______


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