How to write macro to set the chart scale min/max property?

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

 

[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

 

[Peter T]

It works fine for me. Do you get an error

Regards,
Peter T

 

[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

 

[Chua]

It gave a error msg "Unable to set the MinimumScale property of the axis
class". Not sure what's went wrong.

Regards
Chua

 

[Chua]

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