Help setting the gridlines on a XY Scatter chart with vba
 

I want to format the gridlines but I get an error claiming it
can't change the MinimumScale.
Here is the format I would like to use for the gridlines
.MinimumScale = 0
.MaximumScale = 5
.MinorUnitIsAuto = True
.MajorUnit = 1.667
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone

Here is my code

Private Sub CommandButton1_Click()



Sheets("Report").Activate

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"
.ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=True, ShowCategoryName:=False,
ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=False
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
.HasLegend = False
End With
With Cht.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
' It worked unilt I added This
.MinimumScale = 0
.MaximumScale = 5
.MinorUnitIsAuto = True
.MajorUnit = 1.667
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone End With
With Cht.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
' It worked unilt I added This
.MinimumScale = 0
.MaximumScale = 5
.MinorUnitIsAuto = True
.MajorUnit = 1.667
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone End With

End With
End Sub

Help setting the gridlines on a XY Scatter chart with vba
 

The gridlines use the same scaling parameters as the primary axis. Change
the settings for the axis, and the gridline 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
oups.com...
I want to format the gridlines but I get an error claiming it
can't change the MinimumScale.
Here is the format I would like to use for the gridlines
.MinimumScale = 0
.MaximumScale = 5
.MinorUnitIsAuto = True
.MajorUnit = 1.667
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone

Here is my code

Private Sub CommandButton1_Click()



Sheets("Report").Activate

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"
.ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=True, ShowCategoryName:=False,
ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=False
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
.HasLegend = False
End With
With Cht.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
' It worked unilt I added This
.MinimumScale = 0
.MaximumScale = 5
.MinorUnitIsAuto = True
.MajorUnit = 1.667
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone End With
With Cht.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
' It worked unilt I added This
.MinimumScale = 0
.MaximumScale = 5
.MinorUnitIsAuto = True
.MajorUnit = 1.667
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone End With

End With
End Sub