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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

and then

Please Login or Register  to view this content.

would "pause" for 100ms.

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

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.

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

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.