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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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

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

--
John Mansfield
http://cellmatrix.net





"hlmrspd" wrote:

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

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.

"John Mansfield" wrote:

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

--
John Mansfield
http://cellmatrix.net





"hlmrspd" wrote:

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

Remove the flashing:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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.

"John Mansfield" wrote:

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

--
John Mansfield
http://cellmatrix.net





"hlmrspd" wrote:

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

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

"Jon Peltier" wrote:

Remove the flashing:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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.

"John Mansfield" wrote:

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

--
John Mansfield
http://cellmatrix.net





"hlmrspd" wrote:

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