Y axis scale macro

[Guest]

I have created a macro (with help from this forum) to automatically adjust
the Y axis based on the selected x axis data. The macro works but when the
macro runs it pages through all the excel sheets that I have the charts on.
Is there a change I can make to the macro so it will stay on the sheet that I
have the button on?

Here is the code for the macro:

For I = 3 To 63

sheetname = Range("'Yaxis'!$B$" & I).Value
Sheets(sheetname).Select

A = Range("'Yaxis'!$C$" & I).Value

With ActiveSheet.ChartObjects(A).Chart

With .Axes(xlValue)
.MaximumScale = Range("'Yaxis'!$D$" & I).Value
.MinimumScale = Range("'Yaxis'!$E$" & I).Value
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True

End With

End With

Next

 

[Jon Peltier]

Take out the sheets(blah).select within the loop, or take out the loop
altogether, depending on what is on each sheet.

- Jon

 

[Guest]

As Jon noted, take out all of the sheet references to get to the active
sheet. To set all the charts in the active sheet to the same min, max, and
major units, in general your macro could look something like this:

Sub ChangeScale1()
Dim Cht As ChartObject
For Each Cht In ActiveSheet.ChartObjects
With Cht.Chart.Axes(xlValue)
.MaximumScale = Range("A1").Value
.MinimumScale = Range("A2").Value
.MajorUnit = Range("A3").Value
End With
Next Cht
End Sub

If you want to change just one chart, I prefer to name each chart first and
then make the changes. To name a single chart, use:

Sub NameChart()
ActiveChart.Parent.Name = "Cht1"
End Sub

Then you can use the following to change the scale:

Sub ChangeScale2()
Dim Cht As ChartObject
Set Cht = ActiveSheet.ChartObjects("Cht1")
With Cht.Chart.Axes(xlValue)
.MaximumScale = Range("A1").Value
.MinimumScale = Range("A2").Value
.MajorUnit = Range("A3").Value
End With
End Sub

Alternatively, if you dont feel like naming the chart you can just click on
it (activate it) and use the following:

Sub ChangeScale3()
With ActiveChart.Axes(xlValue)
.MaximumScale = Range("A1").Value
.MinimumScale = Range("A2").Value
.MajorUnit = Range("A3").Value
End With
End Sub

 

[Guest]

I use the first macro to set the scale on the x axis for all the charts.

For the yaxis I need to change 60 charts on 12 sheets and each Yaxis is
unique.

I set up the macro to read the new min and max after I reset the xaxis. The
charts show engineering data and I usually do 30 minute runs but need the
ability to zoom the x axis in so I can evaluate problems the Y axis then
needs to re-scale so I can see where the problem is. As I stated the macro
works but it is annoying watching it flash through 12 different sheets when I
reset the yaxis.

 

[Jon Peltier]

Remove the flashing:

Application.ScreenUpdating = False
' bulk of the procedure
Application.ScreenUpdating = True

- Jon

 

[Guest]

That did the trick.
I'm just now getting back to the project after a long break.
Thanks for your help.