x-axis scale changes

[Laoballer]

I have a macro that automatically generates a new chart from data in a
worksheet. When I run my macro, charts that have been created prior
will have the same min x-axis scale as the new chart generated.
However the recently generated chart looks perfect. How do I make the
old charts and new charts independent from each other? Whenever I
create a new chart I'm using the charts.add function


Set xlChart = Charts.Add
With xlChart
.ChartWizard Source:=GanttChartRange, Gallery:=xlBar,
Format:=3, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels:=1,
HasLegend:=1, Title:=Title, CategoryTitle:="", ValueTitle:="",
ExtraTitle:=""
.SeriesCollection(1).Border.LineStyle = xlNone
.SeriesCollection(1).Interior.ColorIndex = xlNone
.Axes(xlCategory).ReversePlotOrder = True
.Axes(xlCategory).Crosses = 4 'TaskCount
.Axes(xlValue).MinimumScale = minXscale 'minXscale is a cell
on the worksheet min value of x-axis
.Axes(xlValue).TickLabels.Font.Name = "Arial"
.Axes(xlValue).TickLabels.Font.Size = 10
.Axes(xlCategory).TickLabels.Font.Name = "Arial"
.Axes(xlCategory).TickLabels.Font.Size = 12
.Legend.Font.Name = "Arial"
.Legend.Font.Size = 12
.HasTitle = True
.ChartTitle.Text = Range("GanttTitle").Value
.ChartTitle.Font.Name = "Arial"
.ChartTitle.Font.Size = 15
.ChartTitle.Font.Bold = False
End With

this code is courtesy of Dave Steppan,
http://www.geocities.com/SiliconValley/Network/1030/gantt.zip

thanks,

 

[Jon Peltier]

The new charts are perfect, and the old charts are not? What's wrong with
them?

What makes you say that the charts are dependent on one another? There is
some dependence if they use the same data, of course, and if they use the
same minimum date (that's the value/Y axis by the way, not the category/X
axis).

- Jon

 

[Laoballer]

The new charts are perfect, and the old charts are not? What's wrong with
them?

What makes you say that the charts are dependent on one another? There is
some dependence if they use the same data, of course, and if they use the
same minimum date (that's the value/Y axis by the way, not the category/X
axis).

- Jon

You're right, the charts are dependent on the min x value. How do I
make it so that when each chart is created it no longer take on the
min x value in the vba code after it finishes?

 

[Jon Peltier]

To remove the hard-coded minimum, stop setting it, that is, remove this line
of code:

.Axes(xlValue).MinimumScale = minXscale 'minXscale is a cell

In an existing chart, right click on the axis, choose Format, and change the
scale so it is automatic, not fixed to the value you set.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

The new charts are perfect, and the old charts are not? What's wrong with
them?

What makes you say that the charts are dependent on one another? There is
some dependence if they use the same data, of course, and if they use the
same minimum date (that's the value/Y axis by the way, not the category/X
axis).

- Jon

You're right, the charts are dependent on the min x value. How do I
make it so that when each chart is created it no longer take on the
min x value in the vba code after it finishes?