Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manual calculation to Automatic on close - BUT without recalculati
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manual calculation to Automatic on close - BUT without recalculati
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 "Carlos" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic/Manual Calculation | Excel Discussion (Misc queries) | |||
Calculation Manual vs Automatic | Excel Discussion (Misc queries) | |||
Calculation - Automatic and Manual | Excel Discussion (Misc queries) | |||
manual vs. automatic calculation | Excel Programming | |||
Manual / Automatic Calculation | Excel Programming |