Disable Mouse and Keyboard in Excel while executing code

Add-in Express™ Support Service
That's what is more important than anything else

Disable Mouse and Keyboard in Excel while executing code
 
tttmack




Posts: 12
 Joined: 2015-02-17
Hello Andrei,

I thought that setting ScreenUpdating and EnableEvents equal to false while executing code would disable all keyboard and mouse events while code is executing, but I was surprised to see that that was not the case.

I have created a simple example that takes a few seconds to execute. While it is executing, if you do anything with your mouse or keyboard, the action does not happen immediately but seems to queue up and execute after the code finishes executing.

For example, if I click cell D5 while the code is running and press delete, it will delete cell D5 immediately after my code finishes executing; likewise, clicking the Excel Close button while the code is executing will queue up a call to close Excel and will prompt me if I want to save before closing Excel.

Here is the simple code I am using to test this: 


        private void adxRibbonButton1_OnClick(object sender, IRibbonControl control, bool pressed)
        {
            var App = (Excel.Application) ExcelApp;
            ExcelApp.ScreenUpdating = false;
            ExcelApp.EnableEvents = false;
            ExcelApp.Cursor = Excel.XlMousePointer.xlWait;

            int rows = 100000;
            int cols = 10;
            var a = new string[rows, cols];
            for(int i=0; i<rows; i++)
                for (int j = 0; j < cols; j++)
                    a[i, j] = "String" + (i*cols + j + 1);

            var sht = (Excel.Worksheet)App.ActiveSheet;
            var rngStart = sht.Cells[1, 1];
            var rngEnd = sht.Cells[rows, cols];
            var rng = sht.Range[rngStart, rngEnd];
            rng.Value2 = a;

            Marshal.ReleaseComObject(sht);
            Marshal.ReleaseComObject(rngStart);
            Marshal.ReleaseComObject(rngEnd);
            Marshal.ReleaseComObject(rng);

            ExcelApp.Cursor = Excel.XlMousePointer.xlDefault;
            ExcelApp.ScreenUpdating = true;
            ExcelApp.EnableEvents = true;
        }


What is the correct way to disable all keyboard and mouse events while code is executing?

Many thanks!
Posted 27 Mar, 2015 00:46:51 Top
Andrei Smolin


Add-in Express team


Posts: 18816
 Joined: 2006-05-11
Hello,

I've also tried setting Interactive=false. This doesn't help: ass soon as I restore the value, Excel performs the keyboard commands. You can undo these commands. Below is a raw example in VB.NET. The code finds the Undo control on the Standard commandbar, casts it to Microsoft.Office.Core.CommandBarComboBox, retrieves the top element of the Undo list, checks it and calls CommandBarComboBox.Execute() - this performs the undo operation. CType is the VB.NET way of casting. 

Dim obj As Office.CommandBarComboBox
obj = CType(ExcelApp.CommandBars("Standard").FindControl(, 128), Microsoft.Office.Core.CommandBarComboBox)
If obj.List(1) = "{some string}" Then
    obj.Execute()
End If



Andrei Smolin
Add-in Express Team Leader
Posted 27 Mar, 2015 09:44:26 Top