Macro runs using F8 but Open method fails when using keyboard shortcut

[jamieboss]

Hi there,

I've had this problem before where a macro runs perfectly when I step through it line by line using F8 but fails when I use the recorded keyboard shortcut. Previously the issue was due to a bug in vba where i was using the 'alt' button in the keyboard shorcut however this macro uses "Ctrl + j" The code is pasted below. I have highlighted the line that is the problem in red:

Sub UpdateMasterlistItem()
'
' UpdateMasterlistItem Macro
' Macro recorded 7/8/2011 by Jamie Edwards
'
' Keyboard Shortcut: Ctrl+j
Dim VesselID As String
Dim sUsername As String

Sheets("Mast").Select

'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

'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

sUsername = Environ$("username")
If sUsername = "Jamie Edwards" Then

'Opens the masterlist file and searches for the correct line
ChDir "C:\Bar-i\Clients\DropBox"

'Here's the problem line
Workbooks.Open Filename:= _
"C:\Bar-i\Clients\DropBox\MasterlistUpdates.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

Else

End If

End Sub

 

[jamieboss]

For some reason, changing that line I have in red to

Application.Workbooks.Open ("C:\Bar-i\Clients\DropBox\Bar-i Masterlist.xls")

makes it work fine.