check calculation state on workbook open

[werafa]

Hi all,

I am trying to check (and set to automatic) the calculation state when a workbook is opened.

I've created a personal macro workbook
I've written the following sub (which works) which is called by the workbook open event.
the problem is, when first opening excel, the workbook open event triggers before application.calculation is initialised.

does anyone have any advice?
thanks



Sub CheckCalculationState()
'read excel calculation state
'warn user if calculation is not automatic, and offer fix

Dim bIsAuto As Boolean

'ID calculation state
bIsAuto = False
If Application.Calculation = xlCalculationAutomatic Then bIsAuto = True

'warn/fix calculation state
If bIsAuto = False Then
Select Case MsgBox("xlCalculation is not set to automatic. Do you want to set it to set it to Automatic?", vbYesNo Or vbInformation, "clCalculation State")

Case vbYes
Application.Calculation = xlCalculationAutomatic

Case vbNo
'do nothing

End Select
End If
End Sub

[p45cal]

Perhaps use OnTime, scheduling a run of CheckCalculationState at Now() + timevalue("00:00:02")That's a 2 second delay - experiment with different times too maybe?

[snb]

If I close Excel and open it again the calcultation is always set to 'automatic'.
No need to check or alter anything.

[SamT]

Call your sub from

Workbook_SheetActivate(ByVal Sh As Object)
If Not Application.Calculation = xlCalculationAutomatic Then CheckCalculationState
End sub

[werafa]

Thanks p45Cal

this might be working.
and for anyone else reading this, if, for any reason, you save a workbook with calculation turned off, excel remembers and starts to apply this setting to everything you open.
this can really mess with your head.

[werafa]

mr SamT,

I think your solution would also work.
this would prevent the sub from firing until the object was initialised.

werafa

[SamT]

It can be such an issue.

Since I work with so many "Not quite right" Workbooks from others, I have a sub in my Personal workbook that sets all Persistent functions back to normal and it can be run manually, but it does run every time Personal opens.