EXCEL 2007 CHART REFRESH 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 convince
my company to revert back to Office 2003)?

 

[Tushar Mehta]

See the replies to your post in .programming.

--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

 

[Piers]

Re: EXCEL CHART REFRESH WHILE MACRO IS RUNNING

I had the same problem, found two solutions. At the point you want the chart refreshed, add either of the following lines:

Application.Calculate

or

ActiveSheet.Calculate

I also found that if a macro uses the ClearContents method on any cells in the sheet containing the chart then the chart gets refreshed. Happy days!