Update Thermometer Chart Via Macro

[xl_psychic]

All:

I am using a thermometer type of chart to update the progress bar status of a calculation being done by macro...

I am making use of the % of data cells updated as the level of progress bar.. I have attached the chart value to a cell on the sheet which is updated as the macro runs...

The problem is that the chart does not refresh real time while I can see the cell change the value as the macro runs..

However when I run the macro in the Break Mode I can see the chart changing the progress bar...

Any workarounds to making this work..

Help appreciated..

 

[Greg Truby]

Your keyword is DoEvents a Search here for that term + terms like "Progress Indicator" should turn up plenty of info.

 

[xl_psychic]

Greg:

Thanks for the reply..

I have checked the posts that turned up as results to the keywords suggested by you.. Most of them speak about userforms..

Just wondering if DoEvents would work with Charts placed on the Worksheet as is the situation in my case.

Help Appreciated..

 

[Greg Truby]

I haven't tried it in updating a chart-as-progress-indicator context; nor have I the time today to develop a test wb. However, if you look up DoEvents in VB help, you can see what it does. So my guess was that this would work. The implication of your post is that you tried it and it did not work; however you do not state this explicitely. Can you confirm whether you put a DoEvents after the statement(s) designed to alter the appearance of your chart and it didn't work?

Also, the reason you see DoEvents in conjunction with UserForms is that Progress Indicators are normally displayed in the form of UserForms or else something in the Status Bar. A chart on a worksheet or a chart on a chart sheet is not a commonly used solution for indicating progress.

HTH

 

[Andrew Poulsom]

Is Calculation set to Manual in your workbook? When I tested on Automatic the chart updated when the cell changed. Otherwise I had to put:

Application.Calculate

in the update loop.

 

[xl_psychic]

Greg/Andrew:

I used the DoEvents command as suggested and it worked absolutely perfectly..

I do not think I had the Calculation set to Manual.. But let me see if I can get the same result using Andrew's suggestion of explicitly saying Application.Calculate in the code..

Thank you very much for your suggestions guys..