Macro hangs up

**LonnieP** · 10-07-2010, 02:08 PM

I've got a macro that copies a block (range) of spreadsheet from disk and pastes it in the currently active spreadsheet. It runs fine using the RUN button in the VBA editor, but hangs right after opening the source file but before copying RANGE1, when I activate it with CTRL-SHIFT-L.

Here is my code

Please Login or Register  to view this content.

**LonnieP** · 10-07-2010, 02:48 PM

OK, more info. Just recorded another macro with different hot-keys (CTRL-L), and copied the original code into it. Ran fine. Any ideas why the CTRL-SHIFT-L won't work, but CTRL-L does?

**MarvinP** · 10-08-2010, 10:33 AM

Hi LonnieP

Searching the net Ctrl+Shift+L turns on and off the List Filter.
http://www.youtube.com/watch?v=otGlfHEcvOU
Perhaps your macro works when there is no AutoFilter active but not when it is?

**LonnieP** · 10-08-2010, 11:55 AM

Just checked that out - no autofiltering going on at time of activation and this is the only macro running so filtering doesn't get turned on some other place.

Check my understanding of Macro hot keys here. If you assign a macro to CTRL-c that will take precedence over the keyboard copy command (CTRL-c) for that spreadsheet, if that's the only place you keep that macro. If you were to open a different spreadsheet CTRL-c would function normally. If CTRL-c is defined in your personal.xls spreadsheet, well that's another story.

Am I correct on the above?

Still having the hang up problem. Runs fine when assigned to CTRL-l, and from the VBA editor RUN button, but not CTRL-SHIFT-L.

LonnieP

**MarvinP** · 10-08-2010, 07:00 PM

Ok LonnieP,

I have some more suggestions. Look at DoEvents. I've had Windows timming issues where in SendKeys the Key is sent before the correct screen is displayed. The SendKey event key code is just flushed out of the keyboard buffer. Where or when it gets pushed out and to what program has always confused me. By putting a DoEvents in the code it waits until all the windows stuff is done and it comes back to Excel.

I've had problems with Filtering Tables. If I try to filter a range and there is no table I get an error. If I turn the autofilter off and run the code it works. This is also true for selecting a cell in a hidden row or column. Code can sometimes try to do something you can't do if you are using keystrokes. When this happens you get errors in the code.

So the question is, why the heck does a Ctrl-Shift-L give an error and running from the VBA window not. DoEvents to let that keyboard buffer empty or not have it effect the code. My guess is that Ctrl+Shift+L is getting pushed to the file just or being opened.

See http://en.allexperts.com/q/Visual-Ba...s-Function.htm
also http://support.microsoft.com/kb/118468