Excel vba - Disable Mouse Events

Question

I'm developing an Excel 2010 workbook, in a manual formulas calculation mode.

file -> options ->formulas ->Workbook calculation -> manual

However, I want some menu choices to cause recalculation of the workbook.

So I'm using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    Application.EnableEvents = False

    If SOME_CONDITION

        Application.Calculate
        ActiveSheet.AutoFilter.ApplyFilter

    End If

    Application.EnableEvents = True

End Sub

The problem is that if I'm scrolling the middle mouse button, or clicking on a cell while this function is executed, the calculation is abruptly terminated before completion, which is unacceptable.

It seems that the line Application.EnableEvents=False doesn't prevent the mouse events from being fired, and I wasn't able to find any alternative which will prevent this bug.

So what I need is either a way to block all events during the calculation, or somehow prevent the fired events from disrupting the calculation (as it is when the workbook formulas calculation is not manual).

I'd appreciate your help very much!

Thanks.

Matan.

Answer 1

Try this:

Application.Interactive = False 

While the macro is running. Turn it back to true when done.