Clear indicator when manual calculation on

[mr_hiboy]

Hello all,

Bet most of us have been caught by jumping from a file with calculations turned off to another file expecting calculations to be on, printing some work and going into a meeting with a pile of nonsense!

Is there anyway, without added something to each and every excel file, that Excel could make it clear that calcs are turned off e.g. changing the colour of the menu bar or similar.

I've googled but nothing found.

Cheers
Paul

 

[SirJB7]

Hi, mr_hiboy!

If you don't care about converting all your files in .xlsm, you can add this code in the workbook class module (ThisWorkbook):

Option Explicit

Private Sub Workbook_Activate()
    Application.Calculation = xlCalculationAutomatic
End Sub

Ooops... I didn't finished reading your 2nd paragraph. Maybe something like this in the personal macro workbook:

Option Explicit

Private Sub Workbook_Open()
    ForceAuto
End Sub

Sub ForceAuto()
    Const ksDeltaTime = "00:01:00"
    With Application
        .Calculation = xlCalculationAutomatic
        .OnTime Now() + TimeValue(ksDeltaTime), "ForceAuto"
    End With
End Sub

There it'll be reset each minute, adjust the interval properly, so take care when needing manual calculation. In this case you could start Excel without the personal macro workbook enables, or add a message box at the beginning of the open event code to ask whether or not it'd be enabled, or... there're a lot of workarounds, just be aware of that fact.

Regards!