UPPER CASE macro shortcut key anomaly

[beetle]

The following demonstrates that a macro behaves differently if the shortcut
key assigned is UPPER CASE than if the shortcut key assigned is lower case.

o Create four new blank workbooks: c:\temp\a.xls, c:\temp\b.xls,
c:\temp\c.xls, and c:\temp\d.xls
O Put an "a" in cell A1 of workbook a.xls, a "b" in cell A1 of workbook
b.xls, etc.
o In workbook a.xls create two macros as follows:

Sub open_them()
Workbooks.Open Filename:="c:\temp\b.xls"
MsgBox "Opened b.xls"
Workbooks.Open Filename:="c:\temp\c.xls"
MsgBox "Opened c.xls"
Workbooks.Open Filename:="c:\temp\d.xls"
MsgBox "Opened d.xls"
End Sub

Sub close_them()
Windows("b.xls").Activate
ActiveWindow.Close
Windows("c.xls").Activate
ActiveWindow.Close
Windows("d.xls").Activate
ActiveWindow.Close
End Sub

o Assign shortcut key (Tools/Macro/Macros/Options) o (Ctrl+o) to open_them
and c (Ctrl+c) to close_them in workbook a.xls.

o Enter Ctrl+o in workbook a.xls. Click OK to acknowledge the MsgBox for
each of b,c, and d.
The three workbooks b,c, and d are opened as expected.
o Enter Ctrl+c in workbook a.xls to close the auxiliary workbooks b,c, and d.

O Reassign the shortcut key for open_them in workbook a.xls to O (capital
letter O) (Ctrl+Shift+o)
o Enter Ctrl+Shift+o in workbook a.xls. The macro opens workbook b.xls and
proceeds no further.
Neither the MsgBox statement for "Opened b.xls" is displayed nor are
workbooks c.xls or d.xls opened.

o The above sequence fails regardless of what letters are assigned. Any
upper case letter assigned to open_them fails as above.

The problem is easily avoided by not using upper case letters for macro
shortcut keys but is rather perplexing until the cause is identified. I had
recovered/repaired/reinstalled several workbooks and Excel before isolating
the difficulty.

 

[Tom Ogilvy]

This is a well know problem (documented in the Knowledge base or at least it
was - haven't looked lately). If you hold down the Shift Key when you open
a workbook, macros are disable. As to cause; It is assumed (by me) that
this is the same effect you get when using an Uppercase letter in your
shortcut key (because you did Ctrl+Shift+lowercase letter).

 

[Myrna Larson]

This is what MS refers to as an "issue". Holding down the shift key while you
open a workbook manually is a way to disable macros that would otherwise run
after the workbook is opened.

If you assign a shortcut key that requires an upper case letter, Excel
erroneously (IMO) "remembers" the shift key was pressed, and behaves the way
it would if you were opening it manually.

You have figured out the "lesson": don't use upper case letters for shortcut
keys.

IIRC, this problem was elucidated in an article in the MS Knowledge Base,
accessible (with undue difficulty these days) from www.support.microsoft.com

 

[Tom Ogilvy]

Here is one version of it:

http://support.microsoft.com/default.aspx?scid=kb;en-us;175223
XL97: Visual Basic Procedure Halts on Workbooks.Open

--
Regards,
Tom Ogilvy

This is a well know problem (documented in the Knowledge base or at least it
was - haven't looked lately). If you hold down the Shift Key when you open
a workbook, macros are disable. As to cause; It is assumed (by me) that
this is the same effect you get when using an Uppercase letter in your
shortcut key (because you did Ctrl+Shift+lowercase letter).

 

[beetle]

Aha, bitten by a 'creature' feature...thanks for the link to the article.