For Each - loop doesn't work.
 

Hello all,

Could someone help me on this matter. Here is the code, and I don't se
why it is not working. It doesn't loop through the chart objects tha
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 m
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.

Iva

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

For Each - loop doesn't work.
 

just a guess, did you try swapping these lines?

End With
Next myChart

--


Gary


"Intellihome" wrote in message
...

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

For Each - loop doesn't work.
 

Hi Gary,

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

Thanks any way!!!

Iva

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

For Each - loop doesn't work.
 

Ok I guess I will use my workaround in this case. But it is not th
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 tha
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 whol
program down

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