How to set "Manual Calculation" as defauld options

M

MRMX

Hi
My boss received many excel reports everyday. Some worksheet include third part formula like follow
=HPVAL($B$5,$A$5,$A13,C$5,$A$6,$A$7) (Hyperion
So Excel will not ask user wheather he wants re-calculation. So the report became un-readable
Is there a way to set Manual (Tools-Options-Calculation) as defauld option for my boss

Regards
 
M

MRMX

Sorry. I means How to set (Tools-Options-Calculation) to "don't automatice calculation“ when open all excel file?
 
M

MRMX

Sorry. I means How to set (Tools-Options-Calculation) to "don't automatice calculation“ when open all excel file?
 
P

Peo Sjoblom

You have to create a template (see help) with calculation set to manual,
call it Book.xlt and put it in your startup folder. Now
when you open excel it will be set. Or you would need a start up macro in
this particular file that will set it to manual
Of course macros can be bypassed You would put it in ThisWorkbook

Private Sub Workbook_Open()
'code here
End Sub

you can record a macro when you do this and maybe when you turn it back,
then you could have a before close macro with the reset

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'reset code here
End Sub
 
D

Dave Peterson

excel picks up that calculation mode from the first workbook opened in that
session.

I don't think you can change this behavior (or the default). But you can work
around it:

From an earlier post:

You could put a workbook in your XLStart folder that opens and the resets the
calculation mode.

If you just open excel, then there's not a problem. But if you double click on
a file in windows explorer, it looks like you have to do a little fiddling.

This worked ok for me:

Option Explicit
Sub auto_open()
Application.OnTime Now + TimeSerial(0, 0, 5), "resetCalcMode"
End Sub
Sub resetcalcmode()
Application.Calculation = xlCalculationAutomatic
ThisWorkbook.Close savechanges:=False
End Sub

Every workbook in XLStart opens when you open excel. This one waits 5 seconds,
then resets the calculation mode.

When I tried it without the delay, then the calc mode wasn't changed.

And 5 seconds was enough of a delay in my light testing to open the other
workbook. I would think it would be enough of a delay to have to wait for the
manual workbook to start opening.
 
D

Dave Peterson

oops.

Change xlCalculationAutomatic to xlCalculationManual

(I missed it in the subject.)
 
Top