Force charts to refresh?

[Chrisso]

Hi All

I have a quite complex report with about 15 charts fed by sheets with
loads of database functions. (DCOUNTA, DSUM, etc)

I have noticed that sometimes when I change the range names that drive
the analysis (i.e. the month, sales person etc) that some charts
refuse to refresh and show the new data.

That is, when I look at the data worksheet that the chart is based on
the data is updated correctly. However the chart is still showing the
old data before the most recent recalculation.

Is there a way to tell the charts(or a chart) to refresh and ensure
that it is showing the latest data? Ideally I would like to be able to
do this from VB.

Thanks for any ideas in advance.
Chrisso

 

[Shane Devenshire]

Hi,

I'm not aware of any problem with charts refreshing except possibly in 2007.

Just to make sure you can force recalculation by all four of the following
methods (each works differently):

F9 Recalculate all open workbooks
Shift+F9 Calculate active worksheet
Ctrl+Alt+F9 Recalculates all open workbooks regardless of changes
Ctrl+Alt+Shift+F9 Rechecks dependent formulas and then calculates all cells
in all open workbooks, including cells not marked as needing to be calculated.

 

[Jen]

Hi,

I'm in Excel 2007 and have had the same problem. Automatic calculation is
on and pressing F9 doesn't work. We have been able to reproduce the problem
on simple worksheet with no VB and very few formula. The numbers in the
spreadsheet are correct but the chart is still showing the previous values.

Changing to page layout view, altering some values and then changing back to
normal view (a "solution" I found on a different discussion group) seems to
sort the problem for future changes as well as previous, as does crashing the
Excel file and using the autorecover version of the spreadsheet. However,
these are not ideal "solutions" and not every-one will notice that the chart
hasn't updated and certainly won't know to change to layout view.

I'll be looking out for the next office update