Excel 2007 Chart Update While Macro Is Running

[MikeM]

I've written a VBA macro that performs repetetive calculations in an Excel
worksheet. Data from the spreadsheet is displayed in an embedded chart (X-Y
Scatter Plot). I want to watch the chart update while the macro runs.

In Excel 2003 the macro works as intended. In Excel 2007 I see data in the
spreadsheet updating but the chart does not.

I'm running on two different office PCs, Windows XP installed on both,
Office 2003 on one, Office 2007 on the other. I've tried both .xls and .xlsm
file formats on the Office 2007 machine with the same result.

I added a .Chart.Refresh line to my code, but still no joy (the macro runs,
but the chart doesn't update until execution ends).

Can't seem to find any pertinent help. Any suggestions (other than to try
persuading my company it ought to revert back to Office 2003)?

 

[Barb Reinhardt]

Have you tried adding DoEvents to your code?

HTH,
Barb Reinhardt

 

[Tim Williams]

Have you tried adding a DoEvents call ?

Tim

 

[MikeM]

Tim and Barb, thanks for your suggestion. DoEvents did indeed solve the
problem. Naturally, though, execution speed took a hit. Any thoughts on
solutions that might retain more processing speed?

 

[Horst]

I am struggling with exactly the same problem. My animated embedded charts do
no longer update in Excel 2007 but they do this nicely in Excel 2003.

DoEvents does not work for me although I have included it already in the
Excel2003 code. It has nothing to do with the update problem in my case.

I found out that including the code line

ActiveChart.SeriesCollection(1).Select

solves the problem, but leaving the ugly selection marks at least on one
animated curve. I am quite sure that these problems are due to a bug in
Excel2007. This is really a step back compared to the functionality of
Excel2003.

I hope the Mircosoft guys are reading this. So here another annoying
discovery. In Excel2003 curve data points can be checked on hover even for
protect sheets with embedded charts. In Excel2007 the hover option doesn't
work on protected sheets because you have to select at least one curve to get
the hover working for all curves. On protected sheets you cannot selct
curves. This is probably connected to the same bug which causes my animation
problem.

Unless someone has a better solution than mine, I am back to Excel2003.