This is a bit of a difficult one (well for me anyway, which is obvious as I'm looking for help!)
I've got an application that has a button on a user form that does the following:
1. - Opens a specific workbook on a network drive
2. - Gets the next reference and deletes the old reference
3. - Saves and closes this workbook and finally enters this ref into a field on the user form
Copies of this application will be given to more than one user (this is because it also submits data from the user form via an ADO connection to a .mdb file).
But, during some testing today I've found a problem. When two users hit this button to open the workbook that contains the next references at exactly the same time it's allowing them to both open the file (I assume one is opening a copy to get the last ref).
This means that references can potentially be duplicated.
My question is: is there some code that specifies that when the workbook containing the references is opened if it is already open then it throws out an error (either runtime or something I can use in an if statement)? I'm desperate to show that it's impossible for a reference duplication (which I thought I had). The code I used is below:
=================
Private Sub GetRef_Click()
On Error GoTo system_busy
Workbooks.Open Filename:= _
"C:\Pen_ref.xls"
On Error GoTo 0
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A2"), Type:=xlFillDefault
Range("A1:A2").Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
REF = Range("a1").Value
ActiveWorkbook.Save
ActiveWorkbook.Close
Exit Sub
system_busy: 'this is the on error routine
MsgBox "The system is busy at the moment, please try again"
End Sub
=============
Any help is appreciated!!!!
Cheers
Mark
I've got an application that has a button on a user form that does the following:
1. - Opens a specific workbook on a network drive
2. - Gets the next reference and deletes the old reference
3. - Saves and closes this workbook and finally enters this ref into a field on the user form
Copies of this application will be given to more than one user (this is because it also submits data from the user form via an ADO connection to a .mdb file).
But, during some testing today I've found a problem. When two users hit this button to open the workbook that contains the next references at exactly the same time it's allowing them to both open the file (I assume one is opening a copy to get the last ref).
This means that references can potentially be duplicated.
My question is: is there some code that specifies that when the workbook containing the references is opened if it is already open then it throws out an error (either runtime or something I can use in an if statement)? I'm desperate to show that it's impossible for a reference duplication (which I thought I had). The code I used is below:
=================
Private Sub GetRef_Click()
On Error GoTo system_busy
Workbooks.Open Filename:= _
"C:\Pen_ref.xls"
On Error GoTo 0
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A2"), Type:=xlFillDefault
Range("A1:A2").Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
REF = Range("a1").Value
ActiveWorkbook.Save
ActiveWorkbook.Close
Exit Sub
system_busy: 'this is the on error routine
MsgBox "The system is busy at the moment, please try again"
End Sub
=============
Any help is appreciated!!!!
Cheers
Mark