Macros: Step Thru Works, Run Works, Keyboard Shortcut Locks up

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Problem as stated Above
Discovered when I recreated, from scratch,
all macros and source files, on another computer.
It locked up as before on my main computer.
By chance I tried Macro RUN, and lo an behold, it worked.
I have previously submitted a macro for the same process, but
operating differently. It also locked up after file 1.xls was
opened and would just sit there with active cell A1.

SO, how and/or why, can/does a keyboard shortcut
stop a macro from running

Sub Create_MASTER_File()
' KeyBoard ShortCut: Ctrl+Shift+ M
' ToolBar Icon: NONE
' Menu Item: NONE

Dim DestBook As Workbook
Dim mySource As Workbook
Application.ScreenUpdating = False
Application.EnableEvents = False

' Open Workbook and Save As: M2M-MASTER.XLS
Workbooks.Add
ChDir "C:\DATA"
ActiveWorkbook.SaveAs Filename:="C:\DATA\M2M-MASTER.XLS",
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

' The M2M-MASTER must be (will be) the active WB when initiating macro
Set DestBook = ActiveWorkbook
Sheets(1).Activate
Set mySource = Workbooks.Open("C:\DATA\1.xls")

' 1.xls is now open and available
' Go to 1.xls, Sheet #1, Row 1, Copy
' Go to DestBook, Sheet 1, Row 1 and Insert Cut Cells

DestBook.Worksheets(1).Range("A1:IV1").Value =
mySource.Worksheets(1).Range("A1:IV1").Value

' Save only DestBook
With DestBook
.Save
End With

'Save and Close Source Book.xls
With mySource
.Save
.Close Savechanges:=True
End With

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
 
If your keyboard shortcut combination includes the Shift key, remove the Shift
key from the combination.

When you open a workbook and hold down the shift key, excel knows to not run the
open macros (auto_open/workbook_open).

If your shortcut contains that shift key, then excel gets confused and thinks it
should stop.
 
Is there NO end to my lack of Knowledge? (No, DON'T answer that)
Dave, as you explained, removing the 'Shift' worked bang on.
Since I access almost all of my macros via Icons on the toolbar,
i just did a major cleanup of each macro (copy to Textpad, then copy
into a new project) AND I deleted ALL of my Ctrl-Shift-* shortcuts.

Thanks for the prompt response. This one was really driving me nuts.
 
Back
Top