After a lot of search, I managed to do this:

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
ActiveSheet.ChartObjects("Chart 7491").Activate
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
End Sub

The workbook contains many graphs:
- 1 sheet with 1 scatter graph
- 1 sheet with 8 scatter graphs
- 4 sheets with 2 3D-graphs and 23 scatter graphs each
- a couple of sheets without graphs

There are three things I don't know how to do:
1) To assign the same minimum and maximum to the x-axes of
all the scatter graphs in one shot.
2) To change the names of the graphs ("Chart 7491" is awkward).
3) To unselect the graphs after the execution of the macro
(I have to press Escape, or else any movement of the cursor
would drag the graph).

Any help would be much appreciated.

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
For Each ch In ActiveSheet.ChartObjects
ch.Activate
With ActiveChart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
Next
ActiveWindow.Visible = False
Windows(ActiveWorkbook.Name).Activate
ActiveCell.Select
End Sub

Jerry

Sinus Log wrote:

After a lot of search, I managed to do this:

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
ActiveSheet.ChartObjects("Chart 7491").Activate
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
End Sub

The workbook contains many graphs:
- 1 sheet with 1 scatter graph
- 1 sheet with 8 scatter graphs
- 4 sheets with 2 3D-graphs and 23 scatter graphs each
- a couple of sheets without graphs

There are three things I don't know how to do:
1) To assign the same minimum and maximum to the x-axes of all the
scatter graphs in one shot.
2) To change the names of the graphs ("Chart 7491" is awkward).
3) To unselect the graphs after the execution of the macro (I have to
press Escape, or else any movement of the cursor would drag the graph).

Any help would be much appreciated.

Hi,

A slight tweak on Jerry's code.
If you don't select or activate the chart you don't need any code to
unselect it.
I added a test so only xyscatter charts would be processed.

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
For Each ch In ActiveSheet.ChartObjects
Select Case ch.Chart.ChartType
Case xlXYScatter, xlXYScatterLines, xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
With ch.Chart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
End Select
Next
End Sub

Cheers
Andy

Sinus Log wrote:
After a lot of search, I managed to do this:

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
ActiveSheet.ChartObjects("Chart 7491").Activate
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
End Sub

The workbook contains many graphs:
- 1 sheet with 1 scatter graph
- 1 sheet with 8 scatter graphs
- 4 sheets with 2 3D-graphs and 23 scatter graphs each
- a couple of sheets without graphs

There are three things I don't know how to do:
1) To assign the same minimum and maximum to the x-axes of all the
scatter graphs in one shot.
2) To change the names of the graphs ("Chart 7491" is awkward).
3) To unselect the graphs after the execution of the macro (I have to
press Escape, or else any movement of the cursor would drag the graph).

Any help would be much appreciated.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Addressing your requests in reverse order...

Usually there is no need to select and/or activate objects.

Iterating through a collection with the For...Each loop removes the
need to id individual objects.

The For...Each loop also lets one go through each object in a
collection. In this case, there are two collections that need to be
addressed. The sheets in a workbook and the chartobjects in a sheet.

The code below addresses all three of your concerns:

Option Explicit
Option Base 0

Sub doOneChart(aChart As Chart)
With aChart.Axes(xlCategory)
.MinimumScale = Range("param!i35").Value2
.MaximumScale = Range("param!i45").Value2
End With
End Sub
Sub FixAllChartsInActiveWorkbook()
Dim aSheet As Object, aChartObj As ChartObject
For Each aSheet In ActiveWorkbook.Sheets
If TypeOf aSheet Is Chart Then doOneChart aSheet
If TypeOf aSheet Is Chart Or TypeOf aSheet Is Worksheet Then
For Each aChartObj In aSheet.ChartObjects
doOneChart aChartObj.Chart
Next aChartObj
End If
Next aSheet
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , tlobbosNOS-
says...
After a lot of search, I managed to do this:

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
ActiveSheet.ChartObjects("Chart 7491").Activate
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
End Sub

The workbook contains many graphs:
- 1 sheet with 1 scatter graph
- 1 sheet with 8 scatter graphs
- 4 sheets with 2 3D-graphs and 23 scatter graphs each
- a couple of sheets without graphs

There are three things I don't know how to do:
1) To assign the same minimum and maximum to the x-axes of
all the scatter graphs in one shot.
2) To change the names of the graphs ("Chart 7491" is awkward).
3) To unselect the graphs after the execution of the macro
(I have to press Escape, or else any movement of the cursor
would drag the graph).

Any help would be much appreciated.

Tushar Mehta wrote:
Addressing your requests in reverse order...

Usually there is no need to select and/or activate objects.

Iterating through a collection with the For...Each loop removes the
need to id individual objects.

The For...Each loop also lets one go through each object in a
collection. In this case, there are two collections that need to be
addressed. The sheets in a workbook and the chartobjects in a sheet.

The code below addresses all three of your concerns:

Option Explicit
Option Base 0

Sub doOneChart(aChart As Chart)
With aChart.Axes(xlCategory)
.MinimumScale = Range("param!i35").Value2
.MaximumScale = Range("param!i45").Value2
End With
End Sub
Sub FixAllChartsInActiveWorkbook()
Dim aSheet As Object, aChartObj As ChartObject
For Each aSheet In ActiveWorkbook.Sheets
If TypeOf aSheet Is Chart Then doOneChart aSheet
If TypeOf aSheet Is Chart Or TypeOf aSheet Is Worksheet Then
For Each aChartObj In aSheet.ChartObjects
doOneChart aChartObj.Chart
Next aChartObj
End If
Next aSheet
End Sub


I am grateful to you all for your prompt reply.

The variation to Jerry's code introduced by Andy is, in
effect, necessary. Tushar's macro stops executing when it
gets to the 3D-charts.

On the other hand, only Tushar's macro tries executing in
all sheets. So I have a mix of the two:

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
For Each aSheet In ActiveWorkbook.Sheets
For Each ch In aSheet.ChartObjects
Select Case ch.Chart.ChartType
Case xlXYScatter, xlXYScatterLines,
xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
With ch.Chart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
End Select
Next ch
Next aSheet
End Sub

And it works ... too well. But that's my fault. I had
overlooked the fact that 33 of the scatter graphs (11 in 3
sheets) have dates on the x-axis, and must not be modified.
I am sorry about that. So how do I exclude them from macro
execution ? Two ways come to my mind:
- modify their type and exclude the type. I am referring to
the types mentioned in the code above (which I don't
understand): xlXYScatter, xlXYScatterLines,
xlXYScatterLinesNoMarkers, xlXYScatterSmooth,
xlXYScatterSmoothNoMarkers
- exclude them by name.
In any case, I wouldn't know how to do it and still need help.

What about modifying a chart's name ?

See here for naming chartobjects
http://www.andypope.info/tips/tip004.htm

As for not processing the date charts, unless you can tell the scale is
a date rather than a number I think you will have to do this by
excluding charts based on their name. Obviously you will need to name
them first.
This will exclude the scale if it is not a Time series. Trouble is the
default setting is Automatic so this will not work unless you have set
the scale to time series. And to do that I think you need to make the
chart a Line chart in order to get at the option for changing the axis type.
You might be able to test the current Maximum and Minimum values and be
able to tell that the numeric value for a date is not within the range
of your actual data.

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
For Each ch In ActiveSheet.ChartObjects
Select Case ch.Chart.ChartType
Case xlXYScatter, xlXYScatterLines, xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
With ch.Chart.Axes(xlCategory)
' only works if scale has explicitly been set to Time Scale
If .CategoryType < xlTimeScale Then
.MinimumScale = minim
.MaximumScale = maxim
End If
End With
End Select
Next
End Sub

Cheers
Andy

Sinus Log wrote:
Tushar Mehta wrote:

Addressing your requests in reverse order...

Usually there is no need to select and/or activate objects.

Iterating through a collection with the For...Each loop removes the
need to id individual objects.

The For...Each loop also lets one go through each object in a
collection. In this case, there are two collections that need to be
addressed. The sheets in a workbook and the chartobjects in a sheet.

The code below addresses all three of your concerns:

Option Explicit
Option Base 0

Sub doOneChart(aChart As Chart)
With aChart.Axes(xlCategory)
.MinimumScale = Range("param!i35").Value2
.MaximumScale = Range("param!i45").Value2
End With
End Sub
Sub FixAllChartsInActiveWorkbook()
Dim aSheet As Object, aChartObj As ChartObject
For Each aSheet In ActiveWorkbook.Sheets
If TypeOf aSheet Is Chart Then doOneChart aSheet
If TypeOf aSheet Is Chart Or TypeOf aSheet Is Worksheet Then
For Each aChartObj In aSheet.ChartObjects
doOneChart aChartObj.Chart
Next aChartObj
End If
Next aSheet
End Sub


I am grateful to you all for your prompt reply.

The variation to Jerry's code introduced by Andy is, in effect,
necessary. Tushar's macro stops executing when it gets to the 3D-charts.

On the other hand, only Tushar's macro tries executing in all sheets. So
I have a mix of the two:

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
For Each aSheet In ActiveWorkbook.Sheets
For Each ch In aSheet.ChartObjects
Select Case ch.Chart.ChartType
Case xlXYScatter, xlXYScatterLines, xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
With ch.Chart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
End Select
Next ch
Next aSheet
End Sub

And it works ... too well. But that's my fault. I had overlooked the
fact that 33 of the scatter graphs (11 in 3 sheets) have dates on the
x-axis, and must not be modified. I am sorry about that. So how do I
exclude them from macro execution ? Two ways come to my mind:
- modify their type and exclude the type. I am referring to the types
mentioned in the code above (which I don't understand): xlXYScatter,
xlXYScatterLines, xlXYScatterLinesNoMarkers, xlXYScatterSmooth,
xlXYScatterSmoothNoMarkers
- exclude them by name.
In any case, I wouldn't know how to do it and still need help.

What about modifying a chart's name ?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Here's a little thing I recently wrote to tell me the type of axis I'm
dealing with: value (x or y), category (x), or date scale (x).

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''
'' Get Axis Type ''

Public Enum jpAxisType
jpAxisTypeValue = 0
jpAxisTypeDate = 1
jpAxisTypeCategory = 2
End Enum

Public Function GetAxisType(axAxis As Axis) As jpAxisType
Dim vTest As Variant

Select Case axAxis.Type
Case xlValue
GetAxisType = jpAxisTypeValue
Exit Function
Case xlSeriesAxis
GetAxisType = jpAxisTypeCategory
Exit Function
End Select

On Error Resume Next
vTest = axAxis.MaximumScale
If Err.Number < 0 Then
GetAxisType = jpAxisTypeCategory
Exit Function
End If
On Error GoTo 0

On Error Resume Next
vTest = axAxis.TickLabelSpacing
If Err.Number < 0 Then
GetAxisType = jpAxisTypeValue
Exit Function
End If
On Error GoTo 0

GetAxisType = jpAxisTypeDate

End Function
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''

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



Andy Pope wrote:

See here for naming chartobjects
http://www.andypope.info/tips/tip004.htm

As for not processing the date charts, unless you can tell the scale is
a date rather than a number I think you will have to do this by
excluding charts based on their name. Obviously you will need to name
them first.
This will exclude the scale if it is not a Time series. Trouble is the
default setting is Automatic so this will not work unless you have set
the scale to time series. And to do that I think you need to make the
chart a Line chart in order to get at the option for changing the axis
type.
You might be able to test the current Maximum and Minimum values and be
able to tell that the numeric value for a date is not within the range
of your actual data.

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
For Each ch In ActiveSheet.ChartObjects
Select Case ch.Chart.ChartType
Case xlXYScatter, xlXYScatterLines, xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
With ch.Chart.Axes(xlCategory)
' only works if scale has explicitly been set to Time Scale
If .CategoryType < xlTimeScale Then
.MinimumScale = minim
.MaximumScale = maxim
End If
End With
End Select
Next
End Sub

Cheers
Andy

Sinus Log wrote:

Tushar Mehta wrote:

Addressing your requests in reverse order...

Usually there is no need to select and/or activate objects.

Iterating through a collection with the For...Each loop removes the
need to id individual objects.

The For...Each loop also lets one go through each object in a
collection. In this case, there are two collections that need to be
addressed. The sheets in a workbook and the chartobjects in a sheet.

The code below addresses all three of your concerns:

Option Explicit
Option Base 0

Sub doOneChart(aChart As Chart)
With aChart.Axes(xlCategory)
.MinimumScale = Range("param!i35").Value2
.MaximumScale = Range("param!i45").Value2
End With
End Sub
Sub FixAllChartsInActiveWorkbook()
Dim aSheet As Object, aChartObj As ChartObject
For Each aSheet In ActiveWorkbook.Sheets
If TypeOf aSheet Is Chart Then doOneChart aSheet
If TypeOf aSheet Is Chart Or TypeOf aSheet Is Worksheet Then
For Each aChartObj In aSheet.ChartObjects
doOneChart aChartObj.Chart
Next aChartObj
End If
Next aSheet
End Sub


I am grateful to you all for your prompt reply.

The variation to Jerry's code introduced by Andy is, in effect,
necessary. Tushar's macro stops executing when it gets to the 3D-charts.

On the other hand, only Tushar's macro tries executing in all sheets.
So I have a mix of the two:

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
For Each aSheet In ActiveWorkbook.Sheets
For Each ch In aSheet.ChartObjects
Select Case ch.Chart.ChartType
Case xlXYScatter, xlXYScatterLines,
xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
With ch.Chart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
End Select
Next ch
Next aSheet
End Sub

And it works ... too well. But that's my fault. I had overlooked the
fact that 33 of the scatter graphs (11 in 3 sheets) have dates on the
x-axis, and must not be modified. I am sorry about that. So how do I
exclude them from macro execution ? Two ways come to my mind:
- modify their type and exclude the type. I am referring to the types
mentioned in the code above (which I don't understand): xlXYScatter,
xlXYScatterLines, xlXYScatterLinesNoMarkers, xlXYScatterSmooth,
xlXYScatterSmoothNoMarkers
- exclude them by name.
In any case, I wouldn't know how to do it and still need help.

What about modifying a chart's name ?

Jon Peltier wrote:
Here's a little thing I recently wrote to tell me the type of axis I'm
dealing with: value (x or y), category (x), or date scale (x).

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''
'' Get Axis Type ''

Public Enum jpAxisType
jpAxisTypeValue = 0
jpAxisTypeDate = 1
jpAxisTypeCategory = 2
End Enum

Public Function GetAxisType(axAxis As Axis) As jpAxisType
Dim vTest As Variant

Select Case axAxis.Type
Case xlValue
GetAxisType = jpAxisTypeValue
Exit Function
Case xlSeriesAxis
GetAxisType = jpAxisTypeCategory
Exit Function
End Select

On Error Resume Next
vTest = axAxis.MaximumScale
If Err.Number < 0 Then
GetAxisType = jpAxisTypeCategory
Exit Function
End If
On Error GoTo 0

On Error Resume Next
vTest = axAxis.TickLabelSpacing
If Err.Number < 0 Then
GetAxisType = jpAxisTypeValue
Exit Function
End If
On Error GoTo 0

GetAxisType = jpAxisTypeDate

End Function
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''

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



Andy Pope wrote:

See here for naming chartobjects
http://www.andypope.info/tips/tip004.htm

As for not processing the date charts, unless you can tell the scale
is a date rather than a number I think you will have to do this by
excluding charts based on their name. Obviously you will need to name
them first.
This will exclude the scale if it is not a Time series. Trouble is the
default setting is Automatic so this will not work unless you have set
the scale to time series. And to do that I think you need to make the
chart a Line chart in order to get at the option for changing the axis
type.
You might be able to test the current Maximum and Minimum values and
be able to tell that the numeric value for a date is not within the
range of your actual data.

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
For Each ch In ActiveSheet.ChartObjects
Select Case ch.Chart.ChartType
Case xlXYScatter, xlXYScatterLines, xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
With ch.Chart.Axes(xlCategory)
' only works if scale has explicitly been set to Time Scale
If .CategoryType < xlTimeScale Then
.MinimumScale = minim
.MaximumScale = maxim
End If
End With
End Select
Next
End Sub

Cheers
Andy

Sinus Log wrote:

Tushar Mehta wrote:

Addressing your requests in reverse order...

Usually there is no need to select and/or activate objects.

Iterating through a collection with the For...Each loop removes the
need to id individual objects.

The For...Each loop also lets one go through each object in a
collection. In this case, there are two collections that need to be
addressed. The sheets in a workbook and the chartobjects in a sheet.

The code below addresses all three of your concerns:

Option Explicit
Option Base 0

Sub doOneChart(aChart As Chart)
With aChart.Axes(xlCategory)
.MinimumScale = Range("param!i35").Value2
.MaximumScale = Range("param!i45").Value2
End With
End Sub
Sub FixAllChartsInActiveWorkbook()
Dim aSheet As Object, aChartObj As ChartObject
For Each aSheet In ActiveWorkbook.Sheets
If TypeOf aSheet Is Chart Then doOneChart aSheet
If TypeOf aSheet Is Chart Or TypeOf aSheet Is Worksheet Then
For Each aChartObj In aSheet.ChartObjects
doOneChart aChartObj.Chart
Next aChartObj
End If
Next aSheet
End Sub


I am grateful to you all for your prompt reply.

The variation to Jerry's code introduced by Andy is, in effect,
necessary. Tushar's macro stops executing when it gets to the 3D-charts.

On the other hand, only Tushar's macro tries executing in all sheets.
So I have a mix of the two:

Sub Macro1()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
For Each aSheet In ActiveWorkbook.Sheets
For Each ch In aSheet.ChartObjects
Select Case ch.Chart.ChartType
Case xlXYScatter, xlXYScatterLines,
xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
With ch.Chart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
End Select
Next ch
Next aSheet
End Sub

And it works ... too well. But that's my fault. I had overlooked the
fact that 33 of the scatter graphs (11 in 3 sheets) have dates on the
x-axis, and must not be modified. I am sorry about that. So how do I
exclude them from macro execution ? Two ways come to my mind:
- modify their type and exclude the type. I am referring to the types
mentioned in the code above (which I don't understand): xlXYScatter,
xlXYScatterLines, xlXYScatterLinesNoMarkers, xlXYScatterSmooth,
xlXYScatterSmoothNoMarkers
- exclude them by name.
In any case, I wouldn't know how to do it and still need help.

What about modifying a chart's name ?





Hello to all,

Sorry about the delay with my feedback. I have been
streamlining my workbook. I got rid of 75% of the graphs,
it's much lighter now.

The statement:

Case xlXYScatter, xlXYScatterLines, xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers

in one of the posts gave me an idea. My scatter graphs were
all of the type xlXYScatterSmooth. So I changed the graphs
with dates to the type xlXYScatter (that is with markers
only, no line). The result is that they are not affected by
the macro when xlXYScatter is deleted from the case
statement above. And they look better now.

The final macro goes like this:

Sub SetMinMax()
minim = Range("param!i35").Value2
maxim = Range("param!i45").Value2
For Each aSheet In ActiveWorkbook.Sheets
For Each ch In aSheet.ChartObjects
Select Case ch.Chart.ChartType
Case xlXYScatterLines, xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
With ch.Chart.Axes(xlCategory)
.MinimumScale = minim
.MaximumScale = maxim
End With
End Select
Next ch
Next aSheet
End Sub

I have even found a way to revert to the automatic min and max:
..MinimumScaleIsAuto = True
..MaximumScaleIsAuto = True

Now, everything goes as I want. I am still trying to figure
out logical names for the graphs, but I have already tested
the procedure described in Andy's page: no problem.

I would never have found all that by myself. I thank
everyone of you enthousiastically...