Manual calculation to Automatic on close - BUT without recalculati

[Carlos]

I've a large excel doc (which is about to become a even bigger)

It uses Sumproduct & has loads of lookups.

As such when I open one of my macros changes excel to "manual calculation".
I have "Calculate" & "Update" buttons for users to press to update each sheet.
(note: excel is unable to update the workbook)

I need a macro to change the users settings back to Automatic upon closing
the file.

However it has to be done so that excel DOSEN'T try to re-calculate the
entire wookbook (which it can't do) before closing.

I've looked through the forums & googled intensively. (nothing worse than a
repeat post!)
But I do appologise if this has been answered before.

If its not do-able I'll have to just have a pop-up msgbox.

Thanks in advance

 

[Bernie Deitrick]

You could use the close event to schedule a macro from your Personal.xls to set the calc mode to
auto. Just set it some time in the future, long enough to let the book actually close.

In your workbook (5 second delay):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime Now + TimeValue("00:00:05"), "Personal.xls!ResetCalcs"
End Sub

In your Personal.xls file:

Sub ResetCalcs()
Application.Calculation = xlCalculationAutomatic
End Sub

HTH,
Bernie
MS Excel MVP