Reverting User Session to Automatic Calculation

[Alan]

Hi All,

I have a user model that includes code to switch the user session to
Manual Calculation during use.

I am using two event procedures at the Workbook level to ensure that
this is still the case before the user saves the workbook (for the
next user who might disable macros, but still won't want automatic
calcs on):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

With Application
.Calculation = xlManual
.CalculateBeforeSave = False
End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application
.Calculation = xlManual
.CalculateBeforeSave = True
End With

End Sub


Problem:

This leaves the user session set to manual calculation. If they close
down Excel, and re-open then it resets to automatic calculation, but
if they had two models open including this one, then the manual
calculation setting endures through the session.

I next tried making it manual before save (as above), but resetting to
automatic before close.

However, contrary to what I had expected, Excel appears to close
first, and then save (?)

Is there any other way that I can make the model save without
calculating first, but then revert to automatic calculation? I guess
that I need an event After Save, but the only candidate seems to be
the Close event, which *appears* to occur before saving if I am right.

Any suggestions are most welcome.

Thanks,

Alan.

 

[Don Guillett]

Excel takes calculation mode from the first workbook opened.

 

[Alan]

Excel takes calculation mode from the first workbook opened.

Okay, but I am forcing the calculation mode via VBA, so how do I force
it back again *after* the save event?

Thanks,

Alan.

 

[Tom Ogilvy]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
With Application
.Calculation = xlManual
.CalculateBeforeSave = True
End With
ThisWorkbook.Save
With Application
.Calculation = xlAutomatic
.CalculateBeforeSave = True
End With
ThisWorkbook.Saved = True
Application.EnableEvents = True
End Sub

I think what Don is telling you is that when you open your workbook, it
doesn't make any difference what setting it was saved with unless it is the
first workbook opened. If you want to have a specific setting for the
workbook, you need to set that in the workbook_open event. However, it is
not clear what you want when it is opened - the above will handle the
situation of leaving the application with automatic calculation after the
workbook is closes while saving it with calculation set to manual - as you
described. The beforesave event is not involved.

--
Regards,
Tom Ogilvy