Help with following code creating chart

[Vince]

I am using the code below to update individual points on a chart if they are
outside a specified limit. It works fine, but what I can't seem to figure
out is how to keep the chart from updating/refreshing until the routine has
finished running. Currently the chart flickers and you can see each point
updated along the way. Any suggestions on how to not display the chart until
it is updated and ready to be displayed?

Your help is always appreciated. Code is below:
Vince


Private Sub OptionButton9_Click()
Sheets("Graph").Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Formula = _
"=SERIES(Sheet1!R9C1,Sheet1!R1C2:R1C61,Sheet1!R9C2:R9C61,1)"

intRowSelected = 9
dblUpperSpec = Cells(intRowSelected, 63).Value
dblLowerSpec = Cells(intRowSelected, 64).Value
Call limitcheck(intRowSelected)

End Sub

Private Sub limitcheck(intRowSelected)

For i = 2 To 61
If Cells(intRowSelected, i).Value < dblLowerSpec Then
ActiveChart.SeriesCollection(1).Points(i - 1).Select
With Selection
.MarkerBackgroundColorIndex = 6
.MarkerForegroundColorIndex = 6
.MarkerStyle = xlCircle
.MarkerSize = 8 '5
.Shadow = False
End With
End If
Next
End Sub

[PeterAtherton]

Vince

I've added a few lines to you code below

Private Sub OptionButton9_Click()
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Sheets("Graph").Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Formula = _
"=SERIES(Sheet1!R9C1,Sheet1!R1C2:R1C61,Sheet1!R9C2:R9C61,1)"

intRowSelected = 9
dblUpperSpec = Cells(intRowSelected, 63).Value
dblLowerSpec = Cells(intRowSelected, 64).Value
Call limitcheck(intRowSelected)
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub


"Vince" wrote:

> I am using the code below to update individual points on a chart if they are
> outside a specified limit. It works fine, but what I can't seem to figure
> out is how to keep the chart from updating/refreshing until the routine has
> finished running. Currently the chart flickers and you can see each point
> updated along the way. Any suggestions on how to not display the chart until
> it is updated and ready to be displayed?
>
> Your help is always appreciated. Code is below:
> Vince
>
>
> Private Sub OptionButton9_Click()
> Sheets("Graph").Select
> ActiveChart.SeriesCollection(1).Select
> ActiveChart.SeriesCollection(1).Formula = _
> "=SERIES(Sheet1!R9C1,Sheet1!R1C2:R1C61,Sheet1!R9C2:R9C61,1)"
>
> intRowSelected = 9
> dblUpperSpec = Cells(intRowSelected, 63).Value
> dblLowerSpec = Cells(intRowSelected, 64).Value
> Call limitcheck(intRowSelected)
>
> End Sub
>
> Private Sub limitcheck(intRowSelected)
>
> For i = 2 To 61
> If Cells(intRowSelected, i).Value < dblLowerSpec Then
> ActiveChart.SeriesCollection(1).Points(i - 1).Select
> With Selection
> .MarkerBackgroundColorIndex = 6
> .MarkerForegroundColorIndex = 6
> .MarkerStyle = xlCircle
> .MarkerSize = 8 '5
> .Shadow = False
> End With
> End If
> Next
> End Sub
>

[Vince]

That was what I needed, thanks for the help.

"PeterAtherton" wrote:

> Vince
>
> I've added a few lines to you code below
>
> Private Sub OptionButton9_Click()
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
> Sheets("Graph").Select
> ActiveChart.SeriesCollection(1).Select
> ActiveChart.SeriesCollection(1).Formula = _
> "=SERIES(Sheet1!R9C1,Sheet1!R1C2:R1C61,Sheet1!R9C2:R9C61,1)"
>
> intRowSelected = 9
> dblUpperSpec = Cells(intRowSelected, 63).Value
> dblLowerSpec = Cells(intRowSelected, 64).Value
> Call limitcheck(intRowSelected)
> With Application
> .ScreenUpdating = True
> .Calculation = CalcMode
> End With
>
> End Sub
>
>
> "Vince" wrote:
>
> > I am using the code below to update individual points on a chart if they are
> > outside a specified limit. It works fine, but what I can't seem to figure
> > out is how to keep the chart from updating/refreshing until the routine has
> > finished running. Currently the chart flickers and you can see each point
> > updated along the way. Any suggestions on how to not display the chart until
> > it is updated and ready to be displayed?
> >
> > Your help is always appreciated. Code is below:
> > Vince
> >
> >
> > Private Sub OptionButton9_Click()
> > Sheets("Graph").Select
> > ActiveChart.SeriesCollection(1).Select
> > ActiveChart.SeriesCollection(1).Formula = _
> > "=SERIES(Sheet1!R9C1,Sheet1!R1C2:R1C61,Sheet1!R9C2:R9C61,1)"
> >
> > intRowSelected = 9
> > dblUpperSpec = Cells(intRowSelected, 63).Value
> > dblLowerSpec = Cells(intRowSelected, 64).Value
> > Call limitcheck(intRowSelected)
> >
> > End Sub
> >
> > Private Sub limitcheck(intRowSelected)
> >
> > For i = 2 To 61
> > If Cells(intRowSelected, i).Value < dblLowerSpec Then
> > ActiveChart.SeriesCollection(1).Points(i - 1).Select
> > With Selection
> > .MarkerBackgroundColorIndex = 6
> > .MarkerForegroundColorIndex = 6
> > .MarkerStyle = xlCircle
> > .MarkerSize = 8 '5
> > .Shadow = False
> > End With
> > End If
> > Next
> > End Sub
> >