Excel stops responding to the keyboard after a form closes

This is unusual behaviour, this thread is the only example of someone else seeing this that I have found ....
http://www.excelforum.com/excel-gene...ml#post3799212

I did post a reply there, but have been advised that rules would have me start a new thread.

After Excel stops responding to the keyboard, except for Alt-something shortcuts, Excel can't shut down. I have tried closing files one by one (using alt-F, followed by c) until nothing remains, then Excel hangs if you try Alt-F, x. No error messages or anything else unusual. It is related to VBA code, but without any errors showing it is not de-buggable. I suspect it is caused by event handling in Forms, but I can't properly demonstrate this.

The effect commences after a Form closes, but not every time. The Form is modal.

Has anyone else ever experienced anything like this, or perhaps even found out what is causing it or the remedy?

Thanks very much for reading!

Neil

When you say stops responding to keys, do you have an "egg-timer". Is there any code assigned to either the buttons or the deactivate/close event of the form that might get stuck in a loop? It would be helpful if you could upload an example of a workbook for which this happens.

Ragulduy, thanks so much for your swift reply. No, there is no visual effect at all, just a dead keyboard.
I do get a pulsing egg timer for ever, if I try to close Excel. at this stage I have to use Task Manager to kill it off.

The app in development is structured as follows ...
All the code is in an xla, part of a suite of xlas that produce different output workbooks. In each case the output workbook is passive, that is it contains no code.
In turn the xlas have the benefit of an master xla that provides library functions and also watches over Excel, trapping book and sheet events, recognising if they are generated by one of the passive workbooks, and then requesting code to run in the appropriate xla. So I can't really upload a working example without uploading the whole lot.

I started noticing this effect after putting in some mouse event handlers on the Form, the purpose of which is to re-order sections of a worksheet by dragging and dropping items in a ListBox.

Here is the code for the mouse_up event which appears to be the culprit, code below. I'm really only asking if anyone has ever seen this, because in 10 years I have never done so.

Private Sub lstDocumentTitles_MouseUp(ByVal Button As Integer, ByVal shift As Integer, ByVal x As Single, ByVal Y As Single)
Dim section As SynopsisSection, xlSource As Excel.Range, xlTarget As Excel.Range
Dim OK As Boolean
    
    ' this ListBox event handler moves a section of a worksheet, that corresponds to an item on the list, by dragging and dropping
    ' also re-orders the listbox to reflect changes on the sheet
    
    ' this determins the list index where the drop occured
    dragDrop = lstDocumentTitles.ListIndex
    ' dragstart has previously been set by the "lstDocumentTitles_MouseDown" event handler
    
    'checks that both dragdrop and dragstart hold valid indecees
    OK = Not (dragDrop < 0 Or dragStart < 0)
    'checks that they are not the same (no movement needed)
    OK = OK And dragDrop <> dragStart
    
    If OK Then
        With frmHands.Document.synopsis
            ' gets the section that dragstart refers to
            ' the "section" is a user defined type which includes a range object
            'ListBox indecees are 0 based, the section array is 1 based, hense the "+1"
            If .GetSection(dragStart + 1, section) Then
                'set xlsource to the range contained in the source section
                Set xlSource = section.xlRange.EntireRow
                If Not xlSource Is Nothing Then
                    ' gets the section that dragdrop refers to
                    If .GetSection(dragDrop + 1, section) Then
                        'set xlTarget to the range contained in the target section
                        Set xlTarget = section.xlRange.Rows(1).EntireRow
                        ' do the sheet stuff
                        If Not xlTarget Is Nothing Then
                            With Application
                                .enableEvents = False
                                xlSource.Cut
                                xlTarget.Insert
                                .CutCopyMode = False
                                .enableEvents = True
                            End With
                            'reload the form so it reflects the changes on the sheet by re-running the Activate event
                            'which re-reads the document into the ListBoxes
                            UserForm_Activate
                            ' re-selects the list item in question
                            lstDocumentTitles.ListIndex = dragDrop
                        End If
                    End If
                End If
            End If
        End With
    End If
    'tidy
    'drapdrop was set to zero or above by this event handler
    dragDrop = -1
    'dragging was set to true by the "lstDocumentTitles_MouseDown" event handler
    dragging = False
    'moving was set to true by the "lstDocumentTitles_MouseMove" event handler
    Moving = False
End Sub

So if you comment out the mouseup event then the problem disappears?

Good question. You can't comment out an event handler because it is not called from code but rather by Excel itself.
I have just tried commenting out the actual sheet commands - xlSource.copy & xlTarget.insert - removing the functionality, but that does not stop it.
I also tried hiding and re-showing the form, it is modal, but that's no good either. Finally tried using exit sub as soon as the handler is invoked and so far that gets rid of it.

In a new approach, I have tried delaying the re-showing of the form by 1 sec using Application.ontime
So far so good. It may be that Excel is confused by too many things happening at once.

It looks like you have found the problem/solution... It might be worth trying to use doevents rather than application.ontime to "delay" the code.

Thanks a lot. I have used DoEvents before but I've never been sure what it is actually doing
Funny how externalising the problem can sometimes bring results. I notice you have music involvement, so have I.
https://www.facebook.com/goldtoprecordingstudio

Have now tried DoEvents in various places and either Excel crashes or it is ineffective. I think you may be onto something through, so will look further into that approach.
Thanks again.

N

DoEvents essentially makes the current VBA process "yield" to other processes that are currently in the queue. I think it was designed to be used to (for example) refresh a display counter whilst running a loop but I think you can also use it to avoid issues with code executing the next line(s) before the previous command has been completed. I suggested it as I assumed it would be quicker than using ontime to make it wait a second but if it doesn't work I wouldn't bother too much with it. You may have more success with something like sleep, as you can use time amounts < 1 second which may speed things up whilst still functioning correctly. You need to declare the API:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

and then

Sleep 100

would "pause" for 100ms.

Afraid I can't access facebook links from work but yes, I am an acoustic engineer by trade.

er update - DoEvents makes it happen less often but does not cure completely

All interesting stuff. Finally I put DoEvents right at the top of the Event Handler and it appears to do the trick - so far. Thanks so much.
Does Sleep pause the OS or just the application that calls it?

Just the application as far as I know.

Try putting at the start and end of the procedures.

Sadly not. I have been testing with rapid repetitions of the drag/drop because that is what users will do if they get confident. One good thing about application.ontime or sleep is that it gives Excel a break before the next one hits.