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.

MichaelDavid wrote:

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" wrote:

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



MichaelDavid wrote:

Greetings! The below macro runs perfectly from within the VBE
Editor(RunSub/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(CurrentAc tiveSheet).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

--

Dave Peterson
.


--

Dave Peterson