Stop Workbook opening if already open

[uk747]

I have a macro which when run copies information from one sheet to another.

What i need is when one person is running the macro and their information is being transferred to the database workbook, the 2nd or 3rd or 4th etc who is transferring gets a message which says workbook currently in use please try again. then they keep pressing the macro till it eventually transfers successfully

Application.ScreenUpdating = False
 
 
NewName = ActiveWorkbook.Name
 
Workbooks.Open "S:\Workbook2.xls"
 
If ActiveWorkbook.ReadOnly = True Then
MsgBox "The database is being accessed by another user. Please ty again"
ActiveWindow.Close
Exit Sub
Else
 
'Copy information from Workbook1 
Workbooks(NewName).Sheets("Transfer").Range("A1001:D1001").Copy
Workbooks("Workbook2.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Workbooks("Workbook2.xls").Sheets("Sheet2").Range("A2:I5").Copy
Workbooks(NewName).Sheets("Transfer").Range("A1005").PasteSpecial Paste:=xlPasteValues
 
 
'Save and close YTD Spreadsheet
 
ActiveWorkbook.Save
ActiveWindow.Close
 
Workbooks(NewName).Activate
CutCopyMode = False
ThisWorkbook.Activate
Sheets("Transfer").Select
 
Range("B6").Select
Selection.ClearContents
 
 
 
End If
MsgBox "Thankyou. The data you submitted has been saved sucessfully.", vbInformation, "Saved Data"
 
 
Application.ScreenUpdating = True
 
End Sub

Ive tried Application.DisplayAlerts = False and Application.DisplayAlerts = True <!-- / message --><!-- sig -->

but cant get it to work properly.

 

[uk747]

bump