Discussion:
How to write macro to set the chart scale min/max property?
(too old to reply)
Troubled
2008-08-07 09:35:05 UTC
Permalink
I have been trying to set the minimum and maximum values for the value axis
in Chart1 using macros without success.

The Help file mentioned below VBA but I tried and it doesn't work. Maybe I
missed out writing some steps.

With Charts("Chart1").Axes(xlValue)
.MinimumScale = 10
.MaximumScale = 120
End With

Anyone can help me??? Many Thanks.
Joel
2008-08-07 11:02:01 UTC
Permalink
This is the method if the chart is on the worksheet

ActiveSheet.ChartObjects("Chart 3").Activate
With ActiveChart.Axes(xlCategory)
.MinimumScale = 10
.MaximumScale = 120
End With


The real problem is finding the chart name if is on a worksheet. A chart is
first create as a sheet object and then placed on a worksheet. But the name
is changed when it is moved from a sheet to a an object on a worksheet. the
number increases. Here is one way to verify the name

msgbox(ActiveSheet.ChartObjects(1).name)

The 1 will refere to the first chart on the worksheet. Change the one as
required
Post by Troubled
I have been trying to set the minimum and maximum values for the value axis
in Chart1 using macros without success.
The Help file mentioned below VBA but I tried and it doesn't work. Maybe I
missed out writing some steps.
With Charts("Chart1").Axes(xlValue)
.MinimumScale = 10
.MaximumScale = 120
End With
Anyone can help me??? Many Thanks.
Chua
2008-08-08 00:42:06 UTC
Permalink
Hi Joel,

Thanks for your help. I managed to find the correct chart name on the
worksheet using the method you taught.

However, the VBA still prompting error "Unable to set the MinimumScale
property of the axis class" when I tried to run the program using the code
that you provided.

Hope that you can help me to solve the problem.

Many Thanks
Chua
Post by Joel
This is the method if the chart is on the worksheet
ActiveSheet.ChartObjects("Chart 3").Activate
With ActiveChart.Axes(xlCategory)
.MinimumScale = 10
.MaximumScale = 120
End With
The real problem is finding the chart name if is on a worksheet. A chart is
first create as a sheet object and then placed on a worksheet. But the name
is changed when it is moved from a sheet to a an object on a worksheet. the
number increases. Here is one way to verify the name
msgbox(ActiveSheet.ChartObjects(1).name)
The 1 will refere to the first chart on the worksheet. Change the one as
required
Post by Troubled
I have been trying to set the minimum and maximum values for the value axis
in Chart1 using macros without success.
The Help file mentioned below VBA but I tried and it doesn't work. Maybe I
missed out writing some steps.
With Charts("Chart1").Axes(xlValue)
.MinimumScale = 10
.MaximumScale = 120
End With
Anyone can help me??? Many Thanks.
Chua
2008-08-08 07:35:01 UTC
Permalink
Hi Joel,

Finally solve the puzzle ! !

I managed to set the scale range using another code.....trial and error
luck...*O*

ActiveSheet.ChartObjects("Chart 87").Chart.Axes(xlValue) _
.MaximumScale = Cells(7, 22).Value

ActiveSheet.ChartObjects("Chart 87").Chart.Axes(xlValue) _
.MinimumScale = Cells(7, 41).Value

Thanks for your advice...if not, I will still be searching for the correct
chart name and formula.

Cheers
Chua
Post by Chua
Hi Joel,
Thanks for your help. I managed to find the correct chart name on the
worksheet using the method you taught.
However, the VBA still prompting error "Unable to set the MinimumScale
property of the axis class" when I tried to run the program using the code
that you provided.
Hope that you can help me to solve the problem.
Many Thanks
Chua
Post by Joel
This is the method if the chart is on the worksheet
ActiveSheet.ChartObjects("Chart 3").Activate
With ActiveChart.Axes(xlCategory)
.MinimumScale = 10
.MaximumScale = 120
End With
The real problem is finding the chart name if is on a worksheet. A chart is
first create as a sheet object and then placed on a worksheet. But the name
is changed when it is moved from a sheet to a an object on a worksheet. the
number increases. Here is one way to verify the name
msgbox(ActiveSheet.ChartObjects(1).name)
The 1 will refere to the first chart on the worksheet. Change the one as
required
Post by Troubled
I have been trying to set the minimum and maximum values for the value axis
in Chart1 using macros without success.
The Help file mentioned below VBA but I tried and it doesn't work. Maybe I
missed out writing some steps.
With Charts("Chart1").Axes(xlValue)
.MinimumScale = 10
.MaximumScale = 120
End With
Anyone can help me??? Many Thanks.
Peter T
2008-08-07 11:04:36 UTC
Permalink
It works fine for me. Do you get an error

Regards,
Peter T
Post by Troubled
I have been trying to set the minimum and maximum values for the value axis
in Chart1 using macros without success.
The Help file mentioned below VBA but I tried and it doesn't work. Maybe I
missed out writing some steps.
With Charts("Chart1").Axes(xlValue)
.MinimumScale = 10
.MaximumScale = 120
End With
Anyone can help me??? Many Thanks.
Chua
2008-08-08 00:44:18 UTC
Permalink
It gave a error msg "Unable to set the MinimumScale property of the axis
class". Not sure what's went wrong.

Regards
Chua
Post by Peter T
It works fine for me. Do you get an error
Regards,
Peter T
Post by Troubled
I have been trying to set the minimum and maximum values for the value axis
in Chart1 using macros without success.
The Help file mentioned below VBA but I tried and it doesn't work. Maybe I
missed out writing some steps.
With Charts("Chart1").Axes(xlValue)
.MinimumScale = 10
.MaximumScale = 120
End With
Anyone can help me??? Many Thanks.
Loading...