Area Chart not refreshing/updating properly

  • Thread starter Ronald R. Dodge, Jr.
  • Start date


Ronald R. Dodge, Jr.

Excel 2002, SP3

I am actually using 2 charts to make up the apparence of 1 chart.

The first chart is the area chart so as I can get the goal line to go across
the entire plot area. (This is the chart that I'm having issues with)

The second chart is a line chart, which as an object, is over the top of the
area chart.

Both charts are setup as interpolated, so as lines are drawn from data point
to data point which other fields either being left as blank or having the
formula of "=NA()" are skipped on over as far as line drawing on the charts
is concerned.

Data Source of the area chart is setup as "Charts!B37:p37" (If I put in the
"Q" in place of the "P", line wouldn't go all the way to the right side of
the plot area)
Data Source of the line chart is setup as "Charts!B39:Q39" and

The workbook (among others) is set to "Manual Calculation", which even when
using Shift-F9 manually, or using one of the following 2 VBA codes, the
chart does not update as would be expected:

Note, the m_chtProductionRateAreaChart is the ChartObject



m_wshOperatorCharts.Calculate 'The worksheet the chart and data are on.

One more thing I have noticed. It seems after the VBA code is done with it
all, the Line Chart is selected, and I'm not sure if this is causing a
problem with the area chart or not.

If you will, for rows 37 through 41, each row is it's own row, no 2 rows are
merged together, but every 2 columns between B and S are merged together so
as to be able to have the data on the worksheet centered between the grid
lines on the chart while allowing for both 1st shift and 3rd shift data for
those same days showing side by side below on the worksheet. Yes, this
means data points are shown a bit to the left side rather than centered on
the line chart, but that will be dealt with later as that's just a cosmetic
issue, and I already have an idea how to resolve that issue This goal area
chart not adjusting properly is more of an issue cause it's gives off the
wrong perception by far.

The only way I so far have been able to find to get the chart to update
properly is to manually adjust the Y axis setting to either include or
exclude the tick label. However, by doing that, it would also mean that I
would have to reset the Left and Width properties of the PlotArea on the
chart. I'm already having to set these settings within VBA anyhow on my
chart objects so as they print out properly. The code is adjusting the
MaximumScale and MajorUnit properties on the Value Axis properly as
expected, just not refreshing the graph itself, which would be the values.


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000



Ronald R. Dodge, Jr.

I haven't found a reason why for the issue, but I have found a work around
to the issue.

m_chtProductionRateAreaChart.Chart.SeriesCollection(1).Values =
Array(l_lngTotalGoal, l_lngTotalGoal)

Why I must use the Array method in order for the chart to update properly,
I'm not sure, but so be it. If that's how it has to be done, then that's
how it will be done.


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question