Code runs using F8 or F5 but not using the keyboard shorcuts?

[jamieboss]

Hi there,

I've written a macro which is meant to copy the contents of the current row then open another workbook from a standard location, locate the same line and paste in the copied information. The macro is then meant to save and close the workbook it opened with the changes. The 'VesselID' variable is used to store the value of the unique identifying value which is used to find the correct row in the workbook we paste the info into.

My problem is that the code works perfectly when I run it manually from inside VBA (using either F5 to run the whole script or F8 to run it line by line) but only runs first half of the code when I use the keyboard shortcut i recorded?

Any thoughts or assistance are greatly appreciate! I have pasted the actual code below:

Sub MasterlistItemUpdate()
'
' MasterlistItemUpdate Macro
' Macro recorded 7/8/2011 by Jamie Edwards
'
' Keyboard Shortcut: Ctrl+Shift+M

Dim VesselID As String

'Moves the cursor to the cell on the far left which contains the vessel ID
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select

'Copies the row contents ready to paste into the masterlist file
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Select

'Assigns the variable name to the specfic vessel ID
VesselID = Selection.Value

'Opens the masterlist file and searches for the correct line
Workbooks.Open Filename:= _
"C:\Bar-i\Clients\DropBox\Bar-i Masterlist.xls"
Range("A:A").Select
Selection.Find(What:=VesselID, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select

'Pastes in the updated information then saves and closes the file
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWindow.Close

End Sub

 

[BrandonWLH]

Where in the code does it stop?

 

[jamieboss]

Brandon,

Thanks for your interest but I just figured it out. It's due to a bug in excel which is related to a conflict between using the 'open workbook' method and having the 'Shift' key pressed. In practical terms this means if your macro opens a workbook, you cannot have the Shift key as part of the keyboard shortcut to fire the macro. I simply changed the shortcut from 'Ctrl' + 'Shift' and 'J' to 'Ctrl' + 'J'.

Thanks again,

Jamie

 

[BrandonWLH]

Ahhh... had something similar when I was sending a keypress. Certain situation it would kick out and took forever to figure it was the keypress. Glad you figured it out. Always the best feeling when you do.