is there a code to wait?

[Guest]

i have Register1.xls and register2.xls on seperate computers and data.xls
register1 & 2 feed into data.xls (shared)
i have macro "submitall" that enters info from the register into data.xls
and saves.

is there a way that if register1 is saving/updating data.xls and register2
tries to save/update...that it will not start "submitall" or that it will
wait until the other register is done saving?

 

[Sharad Naik]

In data.xls on a convinient sheet and on a convinient cell, Name the cell
as say "Busy",
and in that cell enter "NO" (w/o quotation marks) and save it.
In your code in register1.xls and register2.xls, both,
just before feeding data, you can do as under:
(Assuming the you named cell Busy in Sheet1)

Dim sTime as Long
With Workbooks("data.xls")
With .Worksheets("Sheet1").Range("Busy")
sTime = Timer
Do Until .Value = "NO"
If Timer > sTime + 20 Then
MsgBox "Waited for 20 seconds, still data.xls is busy" &
_
Chr(13) & "Giving up now. Try later.",
vbCritical
Exit Sub
End If
DoEvents
Loop
.Value = "YES"
End With

' Here onwards Your rest of the code to feed data
'then the add below line at the last
.Worksheets("Sheet1").Range("Busy").Value = "NO"
End With

Sharad