Rescale the Chat using VBA

[Peri]

Can any one help me out in rescaling the Chart using VBA (Visual Basic
Editor) in excel ?

Thanks and Regards,

Peri

 

[Andy Pope]

Hi Peri,

One of the best ways to get started is to use the 'Macro recorder' this
will generate code whilst you perform a task. Note that the code
produced is not the most efficient and will almost certainly require
editing.

Also check out Jon Peltier's pages on charts and VBA.
(http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html)

Cheers
Andy

 

[Peri]

Hi Andy,

I will brief you my situation. Can you please help me out ?

I have an excel sheet with the graph placed on it. I am diplaying the graph
by filling up the values in the cell range say (AA1 to AB10) using the VB
Code. Now I want to rescale the chart dynamically and I will need to set the
name of the X axis, Y axis and the Chart Title.

Can you please help me to solve this ?

Thanks and Regards,

Peri

 

[Jon Peltier]

Peri -

You need to check out these properties:

Chart.HasTitle = True
Chart.ChartTitle.Text = "The Chart Title"

Chart.Axes(xlValue).MaximumScale ' Y Axis
.MinimumScale
.HasTitle = True
.AxisTitle.Text = "Y Axis Title"
same for
Chart.Axes(xlCategory) ' X Axis


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

Hi Andy,

I will brief you my situation. Can you please help me out ?

I have an excel sheet with the graph placed on it. I am diplaying the graph
by filling up the values in the cell range say (AA1 to AB10) using the VB
Code. Now I want to rescale the chart dynamically and I will need to set the
name of the X axis, Y axis and the Chart Title.

Can you please help me to solve this ?

Thanks and Regards,

Peri

 

[Peri]

Thanks Jon.

But when I set the maximum and minimum scale for X axis, it is throwing an
error. The error message is "Unable to set the MaximumScale property of the
Axis class". Can you please help me out ?

Thanks and Regards,

Peri

Peri -

You need to check out these properties:

Chart.HasTitle = True
Chart.ChartTitle.Text = "The Chart Title"

Chart.Axes(xlValue).MaximumScale ' Y Axis
.MinimumScale
.HasTitle = True
.AxisTitle.Text = "Y Axis Title"
same for
Chart.Axes(xlCategory) ' X Axis


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

 

[Andy Pope]

Hi Peri,

Jon's code will not work if you simply cut and pasted it into a routine.
It is more of a guide to the code.

Try this and if you still have problems post back,

Sub X()
With ActiveChart
.HasTitle = True
.ChartTitle.Text = "The Chart Title"
With .Axes(xlValue) ' Y Axis
.MaximumScale = 10 ' your value here
.MinimumScale = 0 ' your value here
.HasTitle = True
.AxisTitle.Text = "Y Axis Title"
End With
End With
End Sub

Cheers
Andy

 

[Peri]

Hi Andy,

This is my code:
---------------

Dim ObjChart As ChartObject

Set ObjChart = Sheet1.ChartObjects("Chart 48")

With ObjChart.Chart
.HasTitle = True
.ChartTitle.Text = "The Chart Title"

'For Y Axis
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Text = "Y Title"
.Axes(xlValue).MinimumScale = 130
.Axes(xlValue).MaximumScale = 170

'For X Axis
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Text = "X Title"
.Axes(xlCategory).MinimumScale = 0
.Axes(xlCategory).MaximumScale = 30
End With

I have no problem in setting for the Y axis. But when I set for the X-Axis,
I am getting this error (for both minumum and maximum).

 

[Andy Pope]

Hi Peri,

You code works fine IF the charttype is XY-scatter.
I would guess you have a Line chart. In which case the X-axis is
actually a category axis and not a value one.

Check and change the chart type. Then try you code again.

Cheers
Andy

Hi Andy,

This is my code:
---------------

Dim ObjChart As ChartObject

Set ObjChart = Sheet1.ChartObjects("Chart 48")

With ObjChart.Chart
.HasTitle = True
.ChartTitle.Text = "The Chart Title"

'For Y Axis
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Text = "Y Title"
.Axes(xlValue).MinimumScale = 130
.Axes(xlValue).MaximumScale = 170

'For X Axis
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Text = "X Title"
.Axes(xlCategory).MinimumScale = 0
.Axes(xlCategory).MaximumScale = 30
End With

I have no problem in setting for the Y axis. But when I set for the X-Axis,
I am getting this error (for both minumum and maximum).

 

[Peri]

Thanks Andy,

It Works !!!

Thanks a lot to you and Jon

Thanks and Regards,

Peri

Hi Peri,

You code works fine IF the charttype is XY-scatter.
I would guess you have a Line chart. In which case the X-axis is
actually a category axis and not a value one.

Check and change the chart type. Then try you code again.

Cheers
Andy