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

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" <[email protected]> wrote in message
news:[email protected]...
>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
>