Help Programming an XY scatter Chart

[aj]

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").Select
Range("D1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("A1:A15,C115"), _
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 Where:=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

 

[Jon Peltier]

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/ChartsHowTo/QuickChartVBA.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]

Thanks alot. This really helps.

 

[aj]

Thanks again. The stuff above really helped. I am having 2 more issues.
1. How do i set the chart location to create a new sheet called
"RiskMatrix" the first time the create chart button is pressed and then
have it replace the old "RiskMatrix" chart everytime it is pressed
again. 2. I want to format the gridlines but I get an error claiming it
can't change the MinimumScale.

Here is what I added to your code
With Cht.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
' This is what I addedd
.MinimumScale = 0
.MaximumScale = 5
.MinorUnitIsAuto = True
.MajorUnit = 1.667
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

 

[Jon Peltier]

1. This code should work:

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

' ignore error if "RiskMatrix" doesn't exist
On Error Resume Next
ActiveWorkbook.Charts("RiskMatrix").Delete
On Error Goto 0

Set WS = ActiveSheet
Set Cht = Charts.Add
Cht.Name = "RiskMatrix"
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

2. You can't adjust the gridline parameters. The gridline uses the axis
parameters, so set these instead, and the gridlines will adjust accordingly.

- 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]

Great. This really helps. Thank you!

 

[aj]

One more question. How do I put a picture from a worksheet in the same
workbook as the chart I am creating in the background of the chart.

I can do it from a file like this:

ActiveChart.PlotArea.Select
Selection.Fill.UserPicture PictureFile:="C:\My
Documents\Picture2.jpg"
Selection.Fill.Visible = True

But how can i grab a picture from a worksheet in the same workbook as
the chart I am creating?