How to avoid chart render on sheet change?

Question

Hi Folks, looking for help on this,

Excel charts normally refresh/render when a chart sheet is activated. (moving from another sheet to the sheet where the chart is placed)

In case I have huge data e.g. chart made from a table of 10000 rows and two columns, even if the source data is not changed the chart re-renders, which takes a while. Is there any way to avoid this?

I have set calculation to manual. I want a way by which the chart will refresh only if I push a button (that should be straight-forward) however not refresh at any other time (may not be so straight-forward)

Applicable to both excel 2003 and 2007.

Thanks guys.

Answer 1

Thanks Daniel.

Well, moving it to different sheets should not make a difference. I have tried that.

I am thinking of the possibility of converting the chart into a simple image on a click of a button so that the rendering process will not be so slow.

This issue is very easy to reproduce if you create a chart with about 5000 data points.

Regards.

Answer 2

Hello.

Maybe you should consider moving the charts on separate sheets ?

Regards.

Daniel

Answer 3

As I see it now, it is not related to calculation, but the graph takes a long time to redraw during sheet switching/scrolling. Is it possible to make the graph behave like a simple image? Even if the chart is disassociated from it's data, for eg. If i copy paste it on another excel workbook with the source workbook closed, it behaves in the same way.

Answer 4

Thanks Vinay.

I understood what you have suggested and tried it out too.

It seems however that chart rendering/calculation is not related to the Applications calculation state. I, in fact keep the application's calculation state to manual itself.

I could send you the sample workbook if you provide me with an address, or, if you can create a chart of 5000 rows and two columns the issue will be simple to reproduce.

Thanks and regards,

Oscar

Answer 5

Are you looking for macro to emulate enabling the manual calculation?

If yes, try the following VBA Script.

Private Sub Workbook_Open()

Application.Calculation = xlCalculateManual

End Sub

To add a refresh button, you can run this VBA script:

Sub Refresh

ThisWorkbook.Sheets("Sheet1").EnableCalculation = True

ThisWorkbook.Sheets("sheet1").EnableCalculation = False

End Sub