How to disable Excel 2010 feature/bug which stops calculation on selection of any cell?

Question

I am working on an excel sheet in which, with the help of an xll addin, I am trying to update the data. I press Ctrl+Alt+F9 to start the calculations, but the calculation stops at any keypress or cell selection. Is there a way to override this feature, or bug?

This does not happen on Excel 2003.

Answer 1

Why do you need to select a cell during calculation - Is there a reason you can't wait until it is done? Selecting/changing cells during calculation is dangerous as the data might not be correctly calculated yet.

If you are just worried about accidental key presses - this should prevent any accidental key presses

Sub SetUpUserInterface()

Dim Current As Worksheet

    For Each Current In Worksheets
        Current.Protect , UserInterfaceOnly:=True
        'Current.Protect Password:="", UserInterfaceOnly:=True
    Next

End Sub

Answer 2

Very good question!

Not sure will this work in your C# addin, but with Excel 2010 Application object model, you can use Application.CalculationInterruptKey to stop interruption on data calculation when a key is pressed. Not tested but this can be it.

Apply this at beginning of CtrlAltF9:

Dim lKey As Long
lKey = Application.CalculationInterruptKey
Application.CalculationInterruptKey = xlNoKey

Then at end of calculation, reset it to what it was or change it to default xlAnyKey.

Application.CalculationInterruptKey = lKey ' Or xlAnyKey

If you have other event triggered Subs, you may want to add lines about Application.CalculationState such that it won't make changes until Application.CalculationState = xlDone.

Hope this helps.

Answer 3

Try turning off the UserControl, EnableEvents and setting the Calculation to Manual:

private static XlCalculation xlCalculation = XlCalculation.xlCalculationAutomatic;
static public void TurnOffApplicationSettings(Excel.Application xlApp)
{
    xlApp.ScreenUpdating = false;
    xlApp.DisplayAlerts = false;
    xlCalculation = xlApp.Calculation; //Record the current Calculation Mode
    xlApp.Calculation = XlCalculation.xlCalculationManual;
    xlApp.UserControl = false;
    xlApp.EnableEvents = false;
}

Then run your long operation and revert the UserControl and EnableEvents to true and the calculation back to Automatic (or more correctly what it was set to previously):

static public void TurnOnApplicationSettings(Excel.Application xlApp)
{
    xlApp.ScreenUpdating = true;
    xlApp.DisplayAlerts = true;
    xlApp.Calculation = xlCalculation;
    xlApp.UserControl = true;
    xlApp.EnableEvents = true;
}