Macro runs within VBE, but does not run to completion via a hotkey

[MichaelDavid]

Greetings! The below macro runs perfectly from within the VBE
Editor(Run>Sub/UserForm), but when run via a hotkey (Ctrl+Shift+K), Excel
gives up after executing the following instruction:
Workbooks.Open FileName:=DataFileToOpen .
That file opens properly but Excel does not execute the rest of the
instructions in the macro. The program doesn't hang, and there are no error
messages.

Sub TestFileOpen()

' Hotkey Ctrl+Shift+K

' Note: ActiveSheet.Name is 19TwinButte
' Note: DataFileToOpen is
"C:\InsiderIndustryResults\BookIssuers19TwinButte.xlsm"
' and it exists.

Dim CurrentActiveSheet As String
Dim DataFileToOpen As String

CurrentActiveSheet = ActiveSheet.Name

' The sheet name is used in forming the file name. Thus the sheet name is
19TwinButte, and the file name is "BookIssuers19TwinButte.xlsm" .
19TwinButte is the name of one of the worksheets in file
"BookIssuers19TwinButte.xlsm"

If Dir("C:\InsiderIndustryResults\BookIssuers" & CurrentActiveSheet &
".xlsm") <> "" Then
DataFileToOpen = "C:\InsiderIndustryResults\BookIssuers" &
CurrentActiveSheet & ".xlsm"
MsgBox "DataFileToOpen is " & DataFileToOpen
Workbooks.Open FileName:=DataFileToOpen
Workbooks("BookIssuers" & CurrentActiveSheet & ".xlsm").
Worksheets(CurrentActiveSheet).Activate
' Copy data from that workbook into active workbook:
Workbooks("BookIssuers" & CurrentActiveSheet &
".xlsm").Worksheets(CurrentActiveSheet).Range("AJ7:AU140").Copy
Workbooks("BookIssuers.xlsm").Worksheets(CurrentActiveSheet).Range("AJ7")

' Now close that workbook:
ActiveWorkbook.Close
End If

End Sub

I will greatly appreciate any suggestions or workarounds.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[Jacob Skaria]

Try

Sub TestFileOpen()
Dim CurrentActiveSheet As String
Dim DataFileToOpen As String
Dim strFolder As String
Dim wb As Workbook

strFolder = "C:\InsiderIndustryResults\BookIssuers\"
CurrentActiveSheet = ActiveSheet.Name


If Dir(strFolder & CurrentActiveSheet & ".xlsm") <> "" Then
DataFileToOpen = strFolder & CurrentActiveSheet & ".xlsm"
MsgBox "DataFileToOpen is " & DataFileToOpen
Set wb = Workbooks.Open(Filename:=DataFileToOpen)
wb.Worksheets(CurrentActiveSheet).Range("AJ7:AU140").Copy _
Workbooks("BookIssuers.xlsm").Worksheets(CurrentActiveSheet).Range("AJ7")
wb.Close
End If

End Sub

 

[Peter T]

It's difficult to deconstruct your macro with those long lines and line
breaks and not sure what "gives up"
means. However try adding the following two lines after the Workbooks.Open
line

Workbooks.Open FileName:=DataFileToOpen
msgbox activeworkbook.name
Exit Sub

Regards,
Peter T

 

[Dave Peterson]

Remove the shift key from the shortcut key combination and try it again.

 

[MichaelDavid]

Hi Jacob:
After I made a minor change (corrected the file name as follows:
strFolder = "C:\InsiderIndustryResults\BookIssuers"), your suggested macro
ran perfectly from the VBE Editor. But when I assigned Ctrl+Shift+K hotkey to
it, it died after opening the file DataFileToOpen. So it exhibits the same
behavior as the way I originally coded the macro.
Next, I changed the hotkey from Ctrl+Shift+K to Ctrl+o, and the macro ran
perfectly. A later suggestion in this thread suggested removing the Shift
from the hotkey. At this moment, as I see it, either Shift is causing the
problem, or I have exceeded the max number of hotkeys allowed. May you have a
blessed day.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[Dave Peterson]

It's the shift key.

If you have a workbook that has a workbook_open event or an auto_open procedure,
then if you open that file with the shift key held down, you stop those
procedures from running.

The shift-key in the shortcut combination confuses excel/vba to stop after you
open a file using one of those shortcut keys.

 

[MichaelDavid]

Hi Dave:
I scoured the internet for a solution, and I found something I posted
back in 2008:
"When the shift key is part of the hotkey sequence used to
call any macro which has a Workbooks.Open command, the Workbooks.Open
command is killed, and execution of the macro is stopped. The infuriating
thing is that Microsoft has known about this problem since about the year
2000, and, apparently hasn't come up with a fix yet--All versions from
Microsoft Excel 2000 up to the present are affected. (See Knowledge Base
article ID 555263, last reviewed by Microsoft on 2/26/05.) The workaround: Do
not use the shift key in any hotkey used to call any Excel macro which
contains a Workbooks.Open command. "

Here is somthing Dave Peterson posted on Wednesday, October 01, 2008 9:09 AM:

"Remove the shift key from the hotkey assignment.

Holding down the shiftkey while you're opening a file tells excel not to run
the
workbook_open event or the auto_open procedure. And it confuses excel so that
it never goes back to finish your code."

Thanks! And may you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

Hi Dave:

From the Microsoft Knowledge Base:

"The workaround for this problem (only applicable on Windows ® platforms) is
to detect whether the shift key is pressed and wait for it to be released
before issuing the Workbooks.Open command:

'Declare API
Declare Function GetKeyState Lib "User32" _
(ByVal vKey As Integer) As Integer
Const SHIFT_KEY = 16

Function ShiftPressed() As Boolean
'Returns True if shift key is pressed
ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function

Sub Demo()
Do While ShiftPressed()
DoEvents
Loop
Workbooks.Open ="C:\My Documents\ShiftKeyDemo.xls"
End Sub
"
(My workaround: Examine all macros for the ones that open files. Remove
Shift from their HotKeys. I found quite a few. Only probem: I have nearly run
out of possible HotKeys. What might be a solution or workaround for that
problem?)
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[Dave Peterson]

Personally, I only have a couple of macros that use shortcut keys--and they
don't open other files.

I'd use a button on a worksheet or a floating toolbar (xl2003 or earlier) or
modify the ribbon/QAT in xl2007 to start macros.