software

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?

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.

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 amp; 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(quot;startquot;)
.MaximumScale = Range(quot;endquot;)
.MajorUnit = Range(quot;major_unitquot;)

.TickLabels.NumberFormat = Range(quot;date_formatquot;)

End With
End Sub

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

...Kelly

quot;garyboomquot; gt; wrote in message
...
gt; I'm trying to dynamically create some charts that move by date. I would
gt; like
gt; to also be able to change the scale of the axis. Basically, I want the
gt; max
gt; value 10 and the min value -10. Is there a way to change the axis values
gt; by
gt; linking to a cell instead of entering a value?
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 MacroActiveSheet.ChartObjects(quot;Chart 4quot;).Activate
ActiveChart.Axes(xlValue).SelectWith ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ActiveSheet.Range(quot;$c$1quot;).Text
End WithWith ActiveChart.Axes(xlCategory)
.MinimumScale = ActiveSheet.Range(quot;$e$41quot;).Value
.MaximumScale = ActiveSheet.Range(quot;$e$42quot;).Value
.MinorUnit = ActiveSheet.Range(quot;$e$43quot;).Value
.MajorUnit = ActiveSheet.Range(quot;$e$44quot;).Value
.Crosses = xlCustom
.CrossesAt = ActiveSheet.Range(quot;$e$41quot;).Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End WithWith ActiveChart.Axes(xlValue)
.MinimumScale = ActiveSheet.Range(quot;$h$42quot;).Value
.MaximumScale = ActiveSheet.Range(quot;$h$41quot;).Value
.MinorUnit = ActiveSheet.Range(quot;$h$43quot;).Value
.MajorUnit = ActiveSheet.Range(quot;$h$44quot;).Value
.Crosses = xlCustom
.CrossesAt = ActiveSheet.Range(quot;$h$42quot;).Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

End SubThis web page describes the process to link cells to axis parameters:

peltiertech.com/Excel/Charts/...nkToSheet.html

Tushar Mehta (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 -
PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
peltiertech.com/Excel/ExcelUserConf06.html
_______

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