Disable Mouse and Keyboard events while executing a macro

[tttmack]

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.

Is there a way to disable all mouse and keyboard interaction with Excel whilst a macro is executing?

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

Private Sub CommandButton1_Click()
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Interactive = False
    Application.Cursor = xlWait
    
    Const Rows As Long = 100000
    Const Cols As Long = 10
    


    Dim MyArray(0 To Rows - 1, 0 To Cols - 1) As String
    
    Dim i, j As Long
    
    For i = 0 To Rows - 1
        For j = 0 To Cols - 1
            MyArray(i, j) = "String" & (i * Cols + j + 1)
        Next j
    Next i
    
    Range(Cells(1, 1), Cells(Rows, Cols)).Value2 = MyArray
    
    Application.Cursor = xlDefault
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Interactive = True
    
End Sub

 

[mole999]

there must be a way of stopping some of it, but when you use Esc to interrupt a code run, disabling the keyboard scan routines totally would be madness

 

[tttmack]

I discovered a property call Application.DataEntryMode which one would think would solve the problem, but it doesn't seem to fix it either: https://msdn.microsoft.com/en-us/library/office/ff194983.aspx

Note that I have also tried locking the cells at the start of the macro and unlocking them at the end.

 

[xenou]

perhaps you could put up a small userform with a message "please wait...". you can make the userform modal such that you can't click outside of the userform while it is active:
https://msdn.microsoft.com/en-us/library/office/gg251819.aspx

 

[tttmack]

I don't think a modal form is going to help either. I tried a modal form that performs the operation immediately after showing and the same behavior occurs.

'Sheet1


Private Sub CommandButton1_Click()
    UserForm1.Show vbModal
End Sub

Private Sub UserForm_Activate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Cursor = xlWait


    Const Rows As Long = 100000
    Const Cols As Long = 10


    Dim MyArray(0 To Rows - 1, 0 To Cols - 1) As String


    Dim i, j As Long


    For i = 0 To Rows - 1
        For j = 0 To Cols - 1
            MyArray(i, j) = "String" & (i * Cols + j + 1)
        Next j
    Next i


    Range(Cells(1, 1), Cells(Rows, Cols)).Value2 = MyArray


    Application.Cursor = xlDefault
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Unload Me 'Close the form
End Sub

 

[xenou]

You probably should provide context. I assume that if the modal form doesn't work, then you don't care about mouse clicks on cells (that can't happen anymore). But keyboard input is still being buffered.

Keyboard input is normally buffered. I don't think you can simply stop that without going to a much lower level of programming.

Why are your users entering data that you (as programmer) wish to discard? Normally, the programmer should not be discarding user input without very good reason.

 

[zin]

This is an old question, but it turns out EnableEvent doesn't disable all events, I solved this by making my own global boolean flag "events" that I can turn on or off with "call enable" or "call disable", it includes a counter so if a sub is called that uses disable, then it calls another sub that uses disable, it keeps track of the "depth" (add -1 if disabling and plus 1 if enabling) and will only enable when appropriate (it gets back to 0).
I have a similar process for screenupdating.
Then I put in every event handler in the form that needs it: "if not events then exit sub", this solved a similar problem I had to ignore events, especially change events when a macro changed the same widget.
In a similar vein, I always wanted an event handler that would fire for every possible event (named like "BEFORE_EVENT") so I could do things whenever any event happened like resetting a MSG label, but there is none, so I wrote my own:

sub event_pre
call msg("")
call state ' alter button.enable based on conditions, etc.
end sub

Similar to the "events" test, this also requires a "call event_pre" in EVERY event handler.

So all my event handlers look like:

widget_change/click
call event_pre
if not events then exit sub
<event handler code>
end sub

Another way to effectively do this locally in the widget:

sub widget_click
static busy as boolean
if busy then exit sub else busy=true
<event code>
busy=false
end sub

One problem with this method is if the form program halts and is restarted, there is no way to clear these static busy flags, w/o a total form shutdown/restart.

zin