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

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