resetting manual calculation across all the workbook

[keithkemble]

If a user sets the calculation to be manual via tools etc is there a way to use "thisworkbook privat sub workbook_change" etc to automatically reset the calculation back to automatic.

I have tried worksheet events but I need a global statement that ensures automatic calculation other than when VB routines are running.


For your ino Vb routines switch back to auto but I need some insurance policy to guarantee auto setting.

[just_jon]

Well, if you don't mind calculating all open workbooks, just stick in a 1-liner in a change event macro saying

Application. Calculation = xlAutomatic

or, just

Calculate

I think that'll do what you want; sorry, do not know how to prevent the calculation from being switched.

[Yogi Anand]

Hi Keith:

It appears you have a pretty good handle on what you are trying to do -- however, unless I am missing something, how about using the Workbook_Open event to ensure calculation is set to Automatic ...

Private Sub Workbook_Open()
    Application.Calculation = xlAutomatic
End Sub

Does it help?

[keithkemble]

Yogi,

I have the open event on but should a VB routine break while on manual calc then I need to reset on the next data entry. The reason is because I have not been able to work out a decent event handler yet.

[keithkemble]

Thanks guys, I have managed to suss it with your help.

I was using the target.calculate which would not work right.
I have now used:-

Private Sub Workbook_sheetChange(ByVal Sh As Object, ByVal target As Range)
Application.Calculation = xlAutomatic

End Sub

Thanks again

[keithkemble]

The routine I posted above works a treat but when you run a VB routine which switches off the calculation the Private workbook routine kicks in every time the data changes within the routine.

What I am ultimely trying to do is speed up the routine.

I switched off calc and screen refresh in my original VBA and it whizzed through, but when I use the private workbook routine it takes ages.

Is there an onevent that works when the workbook is Idle ?

[Yogi Anand]

Hi Keith:

I had a little play with the following two Workbook_Sheet events working together ...

Private Sub Workbook_sheetChange(ByVal Sh As Object, ByVal target As Range)
Application.Calculation = xlAutomatic
[a11].select
End Sub

and

Private Sub Workbook_sheetselectionChange(ByVal Sh As Object, ByVal target As Range)
Application.Calculation = xlManual
End Sub

The first one is fired when a change is made to one of the cells in the sheet
(Calculation.xlAutomatic)

and the second is fired subsequent to the first one (Calculation.xlManual) and stays in effect until a change is made to one of the cells in the sheet.

Please post back to let us know if it works for you!
The first one is fired

[just_jon]

If I understand, in some of your macros you turn off calculation, or need it turned off? But then want it restored in all other cases?

If so, then in those macros where turned calculation to manual you could do something like --

sub main()
...
Application.Calculation = xlManual
On Error GoTo Xit
...
...
Xit:
Application.Calculation = xlAutomatic
End Sub

Of course, the above could be modified to allow for other error handling routines, just as long as any exit from the program went hru a = xlAutomatic statement, or all other error handlers eventually refered to Xit.

[keithkemble]

I have tried all three suggestions with no luck.

They appear to counteract each other and the VBA runs for ages. Normally without error traping and auto calc it takes a second.

On an earlier posting Andrew Poulson advised I tried the resume to kickstart the routine again.

The code I am using is below.
I have an overall routine that calls sub routines. In an ideal world if any routine fails then I want it to reset calc to auto ant reprotect sheet.

The error handling is new to me hence the slow uptake.
As you can see I have an error routine but calc stays on manual when I deliberately cause a routine to fail due to cells being protected.

I wonder if the on error routine should be in all sub routines or whether I should amalgamate all routines into one. It may then work.


Sub cmrmenu()
On Error GoTo error
Application.ScreenUpdating = False
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Sheets
Sh.Unprotect "password"
Next
Application.ScreenUpdating = True
Sheets("menu").Select
Application.Run ("Cmr_Show_Please_Wait")
Application.ScreenUpdating = False

Application.Run "sortroutine.archive"
Application.Calculation = xlCalculationManual
Application.Run "cmrstarts1" ' this is where I deliberately stop it
Application.Run "cmrterms1"
Application.Run "futurestarts1"
Application.Run "futureterms1"
Application.Run "cmrfutureststarts"
Application.Run "cmrfuturestterms"
Application.Run "price_reviews_started"
Application.Run "price_reviews_due"
Application.Run "nonhits"
Application.Run "extrahits"
Application.Run "cmr.localcallouts"
Sheets("menu").Select
Application.Run ("module1.Cmr_Hide_Please_Wait")
Application.ScreenUpdating = True

Application.Run "module1.protectall"
Sheets("menu").Select
Range("A1").Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub
error:

Application.Calculation = xlCalculationAutomatic
Resume
End Sub

[tusharm]

If someone changes their XL environment so that the Calculation setting is something other than automatic, why are you bullying them by setting it back to Automatic?

Originally Posted by keithkemble

Thanks guys, I have managed to suss it with your help.

I was using the target.calculate which would not work right.
I have now used:-

Private Sub Workbook_sheetChange(ByVal Sh As Object, ByVal target As Range)
Application.Calculation = xlAutomatic

End Sub

Thanks again