Help with macro to assign min and max to x-axis

[Sinus Log]

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.

 

[Jerry W. Lewis]

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

 

[Andy Pope]

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

 

[Tushar Mehta]

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

 

[Sinus Log]

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]

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

 

[Jon Peltier]

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/
_______

 

[Sinus Log]

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/
_______

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...

 

[Sinus Log]

I am back again with 2 questions, because I would like my
code to be more specific. I am not using a macro anymore,
the code is embedded in a function. There's no need to read
my previous posts.

1) The first graph in sheet "Def" is an XY scatter smooth
with no markers. I wish to assign a minimum value to the
x-axis (say 10). The following code doesn't do the job. What
should I do instead ?
With ActiveWorkbook.Sheets("Def").ChartObjects(1). _
Chart.Axes(xlValue)
.MinimumScale = 10
End With

2) Several graphs in sheet "Gr" are of the same type as
above. I tried the following, but I get #Value!
For Each gra In ActiveWorkbook.Sheets("Gr").ChartObjects
With gra.Chart.ChartType(xlXYScatterSmoothNoMarkers)
With gra.Chart.Axes(xlValue)
.MinimumScale = 20
End With
End With
Next gra
Other graphs in the same sheet are XY scatter smooth WITH
markers. I don't want them to be assigned the minimum above.

Any help will be greatly appreciated.

 

[Tushar Mehta]

For the x-axis, use Axes(xlCategory) not (xlValue). Instead of
guessing or even asking have XL give you the correct syntax -- use the
macro recorder (Tools | Macro > Record new macro...)

As far as the 2nd problem goes, you might want to understand the
difference between between a With statement and an If statement.

--
Regards,

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

 

[Sinus Log]

For the x-axis, use Axes(xlCategory) not (xlValue). Instead of
guessing or even asking have XL give you the correct syntax -- use the
macro recorder (Tools | Macro > Record new macro...)

As far as the 2nd problem goes, you might want to understand the
difference between between a With statement and an If statement.

I followed your advice and I got working macros:
Sub Macro2()
ActiveSheet.ChartObjects("Comp1").Activate
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 10
End With
End Sub
Sub Macro5()
For Each gra In ActiveWorkbook.Sheets("Gr").ChartObjects
Select Case gra.Chart.ChartType
Case xlXYScatterSmoothNoMarkers
gra.Chart.Axes(xlCategory).MinimumScale=10
Case xlXYScatter
gra.Chart.Axes(xlCategory).MinimumScale=38652
Case Else
End Select
Next gra
End Sub

I also checked the definition of "With" and, of course, it's
not what I thought.

The code above works fine when I write it in a
Worksheet_Change sub:
If Not Intersect(Target,Range("A36:M60")) Is Nothing Then
(code here)
End Sub

But it doesn't work when I write it in a function. Is there
anything I can do about that ?

Thanks a lot

 

[Tushar Mehta]

It's glad my suggestions worked out for you. As far as the problem with
the function goes, are you referring to a user defined function that you
enter in a worksheet cell? If so, by design a function cannot change
anything in the Excel environment. It can only return value(s) for use
in the cell(s) in which the function is entered.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[Sinus Log]

It's glad my suggestions worked out for you. As far as the problem with
the function goes, are you referring to a user defined function that you
enter in a worksheet cell? If so, by design a function cannot change
anything in the Excel environment. It can only return value(s) for use
in the cell(s) in which the function is entered.

the function goes, are you referring to a user defined

function that you

Yes, that's what I meant. OK, thank you very much for your
help, and happy new year.