Can someone check these 5 lines of code?

[TheAntiGates]

It will only take a minute and a half, tops :) I'm going to explain the steps in "level 1 support" detail because sometimes that's the only way to get a response, but for advanced users, the bottom line is that my bizarre observation, that I'd like confirmed or denied for others, is, do you get the same result if you invoke it from Ctrl-shift-X, as you do from invoking any other way? The code:
Sub pmOpenIt()
Application.StatusBar = "running"
Debug.Print "0"
Workbooks.Open "c:\delme.xls", 0 'also can try ,2
Debug.Print "1"
Application.StatusBar = "Done."
End SubThe steps:
- Open Excel.
- Ctrl-N
- Save as c:\delme.xls
- Ctrl-F4
- Ctrl-N
- alt-F11
- insert a module
- Paste the code there from first post above.
- alt-F11
- Set Ctrl-shift-Q (or Ctrl-shift-T or whatever) as shortcut to that sole sub, pmOpenIt
- Run the shortcut. What does the statusbar show?
- Ctrl-F4 (close delme.xls)
- alt-F11
- F5 (run the macro non shortcut)

Very disturbingly, I get only the first debug.print and .statusbar if I run from a shortcut. It seems the code stops after the .Open! Yet if I invoke the macro any other way, it runs fully and both statusbars and debug.prints fire fine. It's so bizarre, I fear corruption.

I tested 2003 and 2007, same result. I varied the Open parameter from 0 to 2, but same result.

Can someone reproduce, or assert non-reproducing? IOW, does the .statusbar or debug.print give a DIFFERENT result when using the shortcut key? Thanks.

[p45cal]

Same happens here in Excel 2010.,
Now try a shortcut using a lower case letter instead…

[TheAntiGates]

Same happens here in Excel 2010.,
Now try a shortcut using a lower case letter instead…I'm hoping that that's tongue in cheek, since you use the shift key in, well, Ctrl-Shift-anything! When setting a shortcut in 2003 you have to hold the shift key to set it too! :bug:

I only see one response for 2010, and with me with 2003 and 2007; if re-verified this may rise to the level of a legitimately reportable bug. What is the official repository for reporting that (and one where they'll actually read it, as disgusting as it is to have to consider that)?

[p45cal]

I'm hoping that that's tongue in cheek
Nope!



When setting a shortcut in 2003 you have to hold the shift key to set it too! :bug:
Not here you don't, neither in setting the Options after Alt+F8, nor when one records a fresh macro



I only see one response for 2010, and with me with 2003 and 2007; if re-verifiedJust tried this in Excel 2003, it behaves the same way



What is the official repository for reporting that (and one where they'll actually read it, as disgusting as it is to have to consider that)?Dunno.

Using a lowercase shortcut seems to allow the macro to run to its end; could it be anything at all to do with the behaviour when you open a file while holding the Shift key down? If memory serves me it prevents macros from autorunning?

[TheAntiGates]

The plot thickens. Wow. Ctrl-shift-Q fails, Ctrl-Q works. Brrrr. OMG. Utterly amazing that you discovered that, scary as it is.

I learned something new, that the shift is not required. Thanks. FWIW, most of the Ctrl-letter combos are already assigned, and a few Ctrl-shift-letters are (see link below). However, if you decide that you still want to anyway, you can still reassign them yourself, and Excel won't even warn you (I recall that Word or something DOES warn or prohibit using shortcut keys already in use).

Keyboard shortcut reference http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx

EDIT: note that the pre-assigned control shift are for non-letters, and when you assign, it must be a letter (try to assign ctrl-shift-1; it won't permit it). However you can bulldoze over the assigned function of Ctrl-N by assigning that to your own macro.

Also interestingly, my first inclination was to believe that maybe your choice of the word "lower case" vs. "holding shift key" was off, but I guess it's okay to describe it that way in regard to SETTING the association. I say that because if you try to make a capital letter by using caps lock, Excel (at least 2003) forces you to associate the shift key anyway. Entering as caps-lock unshifted A gets the same as entering non-caps locked, shifted A. But regardless of caps lock, the shift key seems to be the determinant of shortcut association (EDIT: i.e. shortcut *playback* association).

[Kenneth Hobs]

You are not alone in finding this problem.
http://support.microsoft.com/kb/175223
http://social.msdn.microsoft.com/Forums/en-US/858119bd-e1c7-4b89-8459-940ec9c2d581/macro-behaves-differently-when-invoked-from-a-shortcut-key-and-the-macros-selection-window-altf8?forum=isvvba
http://stackoverflow.com/questions/17409524/excel-vba-the-keyboard-shortcut-i-assigned-on-a-macro-make-my-code-hang-after-o

What will work, not documented in those links, is to put DoEvents before Workbooks.Open.

[TheAntiGates]

To give you specific responses for all your trouble in replying:

http://www.vbaexpress.com/forum/images/misc/quote_icon.png Originally Posted by TheAntiGates http://www.vbaexpress.com/forum/images/buttons/viewpost-right.png (http://www.vbaexpress.com/forum/showthread.php?p=298907#post298907)
I'm hoping that that's tongue in cheek

Nope!



http://www.vbaexpress.com/forum/images/misc/quote_icon.png Originally Posted by TheAntiGates http://www.vbaexpress.com/forum/images/buttons/viewpost-right.png (http://www.vbaexpress.com/forum/showthread.php?p=298907#post298907)
When setting a shortcut in 2003 you have to hold the shift key to set it too! :bug:

Not here you don't, neither in setting the Options after Alt+F8, nor when one records a fresh macro

You're right, I stand corrected.
http://www.vbaexpress.com/forum/images/misc/quote_icon.png Originally Posted by TheAntiGates http://www.vbaexpress.com/forum/images/buttons/viewpost-right.png (http://www.vbaexpress.com/forum/showthread.php?p=298907#post298907)
I only see one response for 2010, and with me with 2003 and 2007; if re-verified

Just tried this in Excel 2003, it behaves the same way

Thank you for the extra effort to check that.
http://www.vbaexpress.com/forum/images/misc/quote_icon.png Originally Posted by TheAntiGates http://www.vbaexpress.com/forum/images/buttons/viewpost-right.png (http://www.vbaexpress.com/forum/showthread.php?p=298907#post298907)
What is the official repository for reporting that (and one where they'll actually read it, as disgusting as it is to have to consider that)?

Dunno.



Using a lowercase shortcut seems to allow the macro to run to its end; could it be anything at all to do with the behaviour when you open a file while holding the Shift key down? If memory serves me it prevents macros from autorunning?Interesting thought, yet I have not had shift key depressed while opening so that's not what's causing the failure here.

This is a gruesome bug.

[TheAntiGates]

You are not alone in finding this problem.
http://support.microsoft.com/kb/175223
http://social.msdn.microsoft.com/Forums/en-US/858119bd-e1c7-4b89-8459-940ec9c2d581/macro-behaves-differently-when-invoked-from-a-shortcut-key-and-the-macros-selection-window-altf8?forum=isvvba
http://stackoverflow.com/questions/17409524/excel-vba-the-keyboard-shortcut-i-assigned-on-a-macro-make-my-code-hang-after-o

What will work, not documented in those links, is to put DoEvents before Workbooks.Open.AWESOME answer Kenneth. Those are pinpoint accurate links and real solutions. Your Google-Jonesification makes mine look silly - I never found any of those.

Note that one poster therein suggested
Application.EnableEvents=False
rather than doevents. That did NOT work for me; DoEvents before the .Open DOES work (i.e. workaround).

Very interestingly, a workaround that I tried earlier was to put DoEvents AFTER the open, thinking that this would "give it time to complete." I even tried
DoEvents: DoEvents: DoEvents: DoEvents
BION! (And no, it didn't fix it.)

Primo, first class answer Ken! Awesome. Glad I asked at the time you would see it !!!

[TheAntiGates]

Additional note on working around the bug: several web discussions have evolved to OnTime (also application.wait). I experimented with this and it's messy and problematic in the "false indications" inherent with asynchronous execution. DoEvents before the .Open is the only clean workaround I see.


http://www.vbaexpress.com/forum/images/misc/quote_icon.png Originally Posted by TheAntiGates http://www.vbaexpress.com/forum/images/buttons/viewpost-right.png (http://www.vbaexpress.com/forum/showthread.php?p=298907#post298907)
What is the official repository for reporting that (and one where they'll actually read it, as disgusting as it is to have to consider that)?

Dunno.

[sarcasm on]Well it's only been a problem since Excel 97 according to one of Kenneth's links. RSN, I suppose. My money's on a fix for it in Excel 2020, to offset the amazingly even further destructive interface changes in v. 2020, like they've done in every version since and including the devastating 2007 release.

[sarcasm intensified]Well I'm sure that the fine MS gurus who are PAID to do this kind of thing spend their spare time in VBAexpress, like we do for free, so they'll read this and get all over it! Or. Not!

[TheAntiGates]

In gratitude for the great answers, here's some tasty goodies for all you big strong shrewd shortcut artisans, maybe a bit less than commonly known:

j-walk's "obscure shortcuts"
http://spreadsheetpage.com/index.php/oddity/obscure_shortcut_keys_and_mouse_clicks/

Also: you can trap them, real easily. In my personal.xls class module "EventClass" I have
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
On Error GoTo crap
Application.OnKey "^1", "someSub"
Application.OnKey "^9", "someotherSub"
Application.OnKey "+^9", "someSub"
'etc.
Exit SubThen just put those subs in personal's Module1. Note that the normal behavior of Control-1 (et al) is prevented when you do this.