Has anyone else come across this and know of a fix?

Here's the CheckOut code, as it comes from Excel 2010 Help...

Sub UseCheckOut(docCheckOut As String)

' Determine if workbook can be checked out.
If Workbooks.CanCheckOut(docCheckOut) = True Then
Workbooks.CheckOut docCheckOut
Else
MsgBox "Unable to check out this document at this time."
End If

End Sub

....For me this 'almost' works. As the requested file opens I get the
Enable Macros prompt, I click Enable and then I see the workbook open
briefly and immediately close.

The only way I've had any degree of success is to use the Open method
aswell...

Sub UseCheckOut(docCheckOut As String)

' Determine if workbook can be checked out.
If Workbooks.CanCheckOut(docCheckOut) = True Then
Workbooks.CheckOut docCheckOut
Workbooks.Open docCheckOut
Else
MsgBox "Unable to check out this document at this time."
End If

End Sub

However, there are a couple of problems with this. Firstly, I get the
Enable macros prompt twice - I don't want users to see that. Secondly,
I want the file to check itself back in when the Before_Close event
fires. If the file closes immediately after being checked out it will
obviously check itself back in. When the code then opens the workbook
with the Open method the file is in the wrong state according to
SharePoint.