Can I reference the min and max values of an axis to a cell?

[Guest]

I'm trying to dynamically create some charts that move by date. I would like
to also be able to change the scale of the axis. Basically, I want the max
value +10 and the min value -10. Is there a way to change the axis values by
linking to a cell instead of entering a value?

 

[Kelly O'Day]

Gary:

You may want to take a look at my tutorial on how to add horizontal lines
with min and max dates. Ihave it set for dates, you could easily adjust to
non-dates.

http://processtrends.com/pg_charts_horizontal_line.htm

The idea is to assign range names for start and end dates as well as major
unit and number format.

When you run the macro, its reads the start and end ranges and assigns those
values to min & max X axis scale. With a little extra effort, you could
create a worksheet change event that triggers a chart refresh any time you
change the start/end dates.

Here's the code I use in that example.

Public Sub x_Axis()
With ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory, xlPrimary)
.MinimumScale = Range("start")
.MaximumScale = Range("end")
.MajorUnit = Range("major_unit")

.TickLabels.NumberFormat = Range("date_format")

End With
End Sub

You'll need to tailor to your situation, however,this should get you
started.

...Kelly

(e-mail address removed)

 

[ers]

Gary,
If your chart is in the same sheet where the reference cells are u can
use
after u change $c$1, $e$41, to where your vaues are. It works for me.
Kelly's approach is more elegant, I have to give it a try.
Good Loock
emil

Sub scales2()
' change scales on chart on the current sheet Macro


ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select


With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ActiveSheet.Range("$c$1").Text
End With


With ActiveChart.Axes(xlCategory)
.MinimumScale = ActiveSheet.Range("$e$41").Value
.MaximumScale = ActiveSheet.Range("$e$42").Value
.MinorUnit = ActiveSheet.Range("$e$43").Value
.MajorUnit = ActiveSheet.Range("$e$44").Value
.Crosses = xlCustom
.CrossesAt = ActiveSheet.Range("$e$41").Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With


With ActiveChart.Axes(xlValue)
.MinimumScale = ActiveSheet.Range("$h$42").Value
.MaximumScale = ActiveSheet.Range("$h$41").Value
.MinorUnit = ActiveSheet.Range("$h$43").Value
.MajorUnit = ActiveSheet.Range("$h$44").Value
.Crosses = xlCustom
.CrossesAt = ActiveSheet.Range("$h$42").Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

End Sub

 

[Jon Peltier]

This web page describes the process to link cells to axis parameters:

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

Tushar Mehta (http://tushar-mehta.com) has a free AutoChart Manager utility
that automates this process.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______