Prevent Excel Calculating:...

[Drofidnas]

Not as easy as it sounds.

I have a large workbook with 60+ sheets.

One sheet pulls info from the others and displays a chart (updating the chart with Worksheet_Change(ByVal Target As Range)) as the data point changes.

I also have a status page which checks for errors and refreshes the result with Worksheet_Activate() every time the sheet is activated.

So there is a lot going on.

I originally had pages of formulas on every sheet and gradually have turned all of these into VBA making the whole workbook operate more quickly. I have macros which zip through every sheet copying data and moving it and all sorts of automations and these move like lightning! But whenever I use the Worksheet_Change(ByVal Target As Range) or _Activate() sheets I get Calculating: x% and it takes about 6 seconds to clear each time.

Weirdly, if I click the mouse, the calculation stops and the result is shown - so the information is there, it is just being held up by this spurious calculating which doesn't appear necessary.

Even with Calculation set to Manual I still get this. What is being calculated?

I have

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

at the start of every macro and the opposite at the end.

Is there something else I can do? Even simulating the mouse click!

Thanks for any help.

Chris

 

[NeonRedSharpie]

With calculation manual, clicking into a cell will recalculate JUST that cell. Are you sure that isn't what's happening?

 

[Drofidnas]

Not as far as I know.
I have a row of 24 values pulled from a particular sheet and they are all present no matter if I click randomly or wait for the calculation to stop.

 

[Drofidnas]

I've just removed a few Application.Calculation = xlCalculationAutomatic statements from key sheets and it seems to have worked.
I don't actually need auto calc because all my calculations are happening in VBA when I import data onto my sheets, I'm just changing a view on one sheet based on the data on the others, so I think I can live without it.

Thanks for your input.