P
Peri
Can any one help me out in rescaling the Chart using VBA (Visual Basic
Editor) in excel ?
Thanks and Regards,
Peri
Editor) in excel ?
Thanks and Regards,
Peri
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
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
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
P
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
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
J
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/
_______
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
R
R.VENKATARAMAN
this solution is suggestesd by an MVP ( if remember Mr. Tushar Mehta) and on
that suggestion
I have prepared a procedure customising to my needs and I have given below.
necessary changes has to be made to suit your needs. This procedure may not
be very efficient but it does give results.
remove $,@ and & from email address
'Customized scaling code for holding file
Public Sub scalingaxes()
Dim low As Range
Dim minprice, maxprice, unit As Integer
Dim roundmin, roundmax As Integer
On Error Resume Next
Windows("abs_holdings.xls").Activate
Worksheets("sheet2").Activate
Dim cell As Range
For Each cell In Range(Range("a5"), Range("a5").End(xlDown))
cell.Activate
Set low = Range(ActiveCell, ActiveCell.End(xlToRight))
minprice = Application.WorksheetFunction.Min(low)
maxprice = Application.WorksheetFunction.Max(low)
minprice = Application.WorksheetFunction.RoundDown((minprice), -1)
maxprice = Application.WorksheetFunction.RoundUp((maxprice), -1)
Dim interval As Integer
interval = maxprice - minprice
MsgBox interval
Select Case interval
Case Is <= 10
unit = 1
Case Is <= 20
unit = 2
Case Is <= 50
unit = 10
Case Is <= 100
unit = 20
Case Else
unit = 50
End Select
Charts(ActiveCell.Value).Activate
With ActiveChart.Axes(xlValue, xlPrimary)
.MaximumScale = maxprice
.MinimumScale = minprice
.MajorUnit = unit
End With
Worksheets("sheet2").Activate
Next
End Sub
that suggestion
I have prepared a procedure customising to my needs and I have given below.
necessary changes has to be made to suit your needs. This procedure may not
be very efficient but it does give results.
remove $,@ and & from email address
'Customized scaling code for holding file
Public Sub scalingaxes()
Dim low As Range
Dim minprice, maxprice, unit As Integer
Dim roundmin, roundmax As Integer
On Error Resume Next
Windows("abs_holdings.xls").Activate
Worksheets("sheet2").Activate
Dim cell As Range
For Each cell In Range(Range("a5"), Range("a5").End(xlDown))
cell.Activate
Set low = Range(ActiveCell, ActiveCell.End(xlToRight))
minprice = Application.WorksheetFunction.Min(low)
maxprice = Application.WorksheetFunction.Max(low)
minprice = Application.WorksheetFunction.RoundDown((minprice), -1)
maxprice = Application.WorksheetFunction.RoundUp((maxprice), -1)
Dim interval As Integer
interval = maxprice - minprice
MsgBox interval
Select Case interval
Case Is <= 10
unit = 1
Case Is <= 20
unit = 2
Case Is <= 50
unit = 10
Case Is <= 100
unit = 20
Case Else
unit = 50
End Select
Charts(ActiveCell.Value).Activate
With ActiveChart.Axes(xlValue, xlPrimary)
.MaximumScale = maxprice
.MinimumScale = minprice
.MajorUnit = unit
End With
Worksheets("sheet2").Activate
Next
End Sub
P
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
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
Jon Peltier said: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/
_______
A
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
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
P
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).
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).
A
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
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).
P
Peri
Thanks Andy,
It Works !!!
Thanks a lot to you and Jon
Thanks and Regards,
Peri
It Works !!!
Thanks a lot to you and Jon
Thanks and Regards,
Peri
Andy Pope said: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