For Each - loop doesn't work.

Hello all,

Could someone help me on this matter. Here is the code, and I don't see
why it is not working. It doesn't loop through the chart objects that
are on the sheet.

Sub SetYScaleAllNew(AutoScale As Boolean, YMax As Long, YMin As Long)
Dim myChart As ChartObject
Dim myChartCollection As ChartObjects

numChart = Worksheets("All").ChartObjects.Count '<<< this gives me
that there are 6 of them
Set myChartCollection = Worksheets("All").ChartObjects
For Each myChart In myChartCollection
With myChart.Chart
If (AutoScale = False) Then
.Axes(2, xlPrimary).CrossesAt = YMin
.Axes(2, xlPrimary).MaximumScale = YMax
.Axes(2, xlPrimary).MinimumScale = YMin

.Axes(2, xlPrimary).MajorUnit = Abs(YMax - YMin) / 10

.Axes(2, xlPrimary).MaximumScaleIsAuto = False
.Axes(2, xlPrimary).MinimumScaleIsAuto = False

Else
.Axes(2, xlPrimary).MajorUnitIsAuto = True
.Axes(2, xlPrimary).MaximumScaleIsAuto = True
.Axes(2, xlPrimary).MinimumScaleIsAuto = True
.Axes(2, xlPrimary).CrossesAt = ActiveChart.Axes(2,
xlPrimary).MinimumScale
End If
End With
Next myChart

End Sub

Thank you in advance.

Ivan


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870574024

just a guess, did you try swapping these lines?

End With
Next myChart

--


Gary


"Intellihome" <[email protected]> wrote in message
news:[email protected]...
>
> Hello all,
>
> Could someone help me on this matter. Here is the code, and I don't see
> why it is not working. It doesn't loop through the chart objects that
> are on the sheet.
>
> Sub SetYScaleAllNew(AutoScale As Boolean, YMax As Long, YMin As Long)
> Dim myChart As ChartObject
> Dim myChartCollection As ChartObjects
>
> numChart = Worksheets("All").ChartObjects.Count '<<< this gives me
> that there are 6 of them
> Set myChartCollection = Worksheets("All").ChartObjects
> For Each myChart In myChartCollection
> With myChart.Chart
> If (AutoScale = False) Then
> Axes(2, xlPrimary).CrossesAt = YMin
> Axes(2, xlPrimary).MaximumScale = YMax
> Axes(2, xlPrimary).MinimumScale = YMin
>
> Axes(2, xlPrimary).MajorUnit = Abs(YMax - YMin) / 10
>
> Axes(2, xlPrimary).MaximumScaleIsAuto = False
> Axes(2, xlPrimary).MinimumScaleIsAuto = False
>
> Else
> Axes(2, xlPrimary).MajorUnitIsAuto = True
> Axes(2, xlPrimary).MaximumScaleIsAuto = True
> Axes(2, xlPrimary).MinimumScaleIsAuto = True
> Axes(2, xlPrimary).CrossesAt = ActiveChart.Axes(2,
> xlPrimary).MinimumScale
> End If
> End With
> Next myChart
>
> End Sub
>
> Thank you in advance.
>
> Ivan
>
>
> --
> Intellihome
> ------------------------------------------------------------------------
> Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
> View this thread: http://www.msusenet.com/t-1870574024
>

Hi Gary,

That will be a syntax error, because WITH statement starts within FOR
loop. So it should be closed within it too.

Thanks any way!!!

Ivan


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870574024

Ok I guess I will use my workaround in this case. But it is not the
best solution.

Here is the code when it works.

Sub SetYScaleAllNew(AutoScale As Boolean, YMax As Long, YMin As Long)


Dim myChartCollection As ChartObjects

numChart = Worksheets("All").ChartObjects.Count '<<< this gives me that
there are 6 of them

Set myChartCollection = Worksheets("All").ChartObjects

For i=1 to numChart

With myChartCollection(i).Chart

If (AutoScale = False) Then

.Axes(2, xlPrimary).CrossesAt = YMin
.Axes(2, xlPrimary).MaximumScale = YMax
.Axes(2, xlPrimary).MinimumScale = YMin
.Axes(2, xlPrimary).MajorUnit = Abs(YMax - YMin) / 10
.Axes(2, xlPrimary).MaximumScaleIsAuto = False
.Axes(2, xlPrimary).MinimumScaleIsAuto = False

Else

.Axes(2, xlPrimary).MajorUnitIsAuto = True
.Axes(2, xlPrimary).MaximumScaleIsAuto = True
.Axes(2, xlPrimary).MinimumScaleIsAuto = True
.Axes(2, xlPrimary).CrossesAt = .Axes(2, xlPrimary).MinimumScale

End If

End With

Next i

End Sub

Let me know if you found how to fix the problem.

P.S. I still can't get EnableEvents to work, and it slows my whole
program down.


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870574024