Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverting User Session to Automatic Calculation
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverting User Session to Automatic Calculation
Excel takes calculation mode from the first workbook opened.
"Alan" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverting User Session to Automatic Calculation
"Don Guillett" wrote in message
... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverting User Session to Automatic Calculation
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 Alan wrote in message ... "Don Guillett" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic Calculation | Excel Worksheet Functions | |||
Startup and template locations in Citrix TS session and local session | Setting up and Configuration of Excel | |||
Formula needed - automatically calculate session time totals for each user | Excel Worksheet Functions | |||
Automatic calculation reverting to manual depending upon who opens | Excel Discussion (Misc queries) | |||
Automatic calculation of user-defined worksheet function | Excel Programming |