Managing errors in VBA
 

I am in the process of building an excel template which links
automatically to an excel database. A macro is involved in transferring
some of the data to the database. 15 different people will have a
template and it is possible that 2 of them may try to update the
database at exactly the same time. This would result in a macro error.
Is it possible that instead of showing the error and taking the user
into VBA, a flag could show up in the template telling the user to try
again in 5 minutes?

Any help would be gratefully received!

James

Managing errors in VBA
 

sounds like something you would incorporate in your code in terms of addin
code to see if the database can be updated before trying to update it.
Without knowing how you are trying to update the database, it would be hard
to provide specifics. If you want to find if a workbook on a shared drive is
opened by another user you could use

http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

--
Regards,
Tom Ogilvy


" wrote:

I am in the process of building an excel template which links
automatically to an excel database. A macro is involved in transferring
some of the data to the database. 15 different people will have a
template and it is possible that 2 of them may try to update the
database at exactly the same time. This would result in a macro error.
Is it possible that instead of showing the error and taking the user
into VBA, a flag could show up in the template telling the user to try
again in 5 minutes?

Any help would be gratefully received!

James

Managing errors in VBA
 

wrote:
I am in the process of building an excel template which links
automatically to an excel database. A macro is involved in transferring
some of the data to the database. 15 different people will have a
template and it is possible that 2 of them may try to update the
database at exactly the same time. This would result in a macro error.
Is it possible that instead of showing the error and taking the user
into VBA, a flag could show up in the template telling the user to try
again in 5 minutes?

Any help would be gratefully received!

James


Here is an example


Sub Example()

Dim SomeStr As String
Dim NewRng As Range

On Error GoTo ExitQuietly
Set NewRng = Application.ActiveCell
'Do something that will produce an error
Do
SomeStr = NewRng
Set NewRng = NewRng.Offset(-1, 0)
'MsgBox NewRng.Address
Loop
Exit Sub
ExitQuietly:

If Err.Number = 1004 Then
MsgBox Err.Description & vbLf & "This has been a test, but try
again in 5 minutes.", vbOKOnly
Else
Resume
End If

End Sub

In this case, I knew the error code to expect when I tried to refer to
an object that didn't exist. So in that case ERR# 1004 I displayed a
msg similar to the one you would want to. All other errors will simply
skip through and resume. I assume this is what you want to do. Hope
that helped...