Trying to turn off auto-calculation in a macro

[JohnnyC]

I have a sub routine that runs really slowly unless the calculation method is set to manual. I can run the sub from the VB window OK (the sub is in Module 1) but if I try to run it from a button on a worksheet I get a "Method 'calculation' of object 'application' failed" error message.

The line of code I'm using is:

Application.Calculation = xlCalculationManual

This is really driving me mad. Can anyone help?

JohnnyC

[Mudface]

It might be that your calculation is already set to manual. Might be worth trying this instead: -

With Application
If .Calculation <> xlCalculationManual Then
.Calculation = xlCalculationManual
End If
End With

[JohnnyC]

...just tried that. Calculation was not already set to manual, it runs through to the next line and gives the same error message.

[Mudface]

Sorry, this runs OK for me, whichever way I call it. Could you post the rest of your code, just to see if something else is causing it?

[JohnnyC]

OK, I've just put a line of code in front of the section you sent me which selects a different worksheet.

That was the only change I made and the macro runs fine now...

Thanks for your help anyway.

[Ivan F Moala]

On 2002-04-26 03:14, JohnnyC wrote:
OK, I've just put a line of code in front of the section you sent me which selects a different worksheet.

That was the only change I made and the macro runs fine now...

Thanks for your help anyway.

Sounds like you have excel97 ??
If so then anothr way around this is to
set your cmd button to;

TakeFocus******* = False

[JohnnyC]

Completely right. I am using Excel 97. That button focus thing must have caused me a LOT of problems!

Thanks for the tip.