Problem: I want to create a backup of a workbook on open and feel there
are four possible error conditions to handle. Each should display a
different message to tell the user what is wrong.
1. disk full
2. disk write protected
3. file already open
4. file is read-only

I have an (partial) error handler designed but find that no matter
which condition actually exists always takes the Case Else path.
Sometimes err.number is "1004" while othertimes it is "0" (as seen in
the message box). code follows.

What am I doing wrong?

Sub Auto_open()

On Error GoTo ErrorHandler
If InStr(1, ThisWorkbook.Name, ".bck") < 1 Then
tmp = InStr(1, ThisWorkbook.Name, ".")
bckName = Left(ThisWorkbook.Name, tmp - 1) + ".bck"
ActiveWorkbook.SaveCopyAs (bckName)
End If
ErrorHandler:
Select Case Err.Number

Case 61

msg = "Disk full. Click Yes to continue without creating a backup."
msg = msg + "Otherwise click No to exit Excel."

response = MsgBox(msg, vbYesNo, "Warning: Disk Full")


If response = 6 Then Exit Sub
Application.Quit
Case Else

r = MsgBox("Error # :" & Err.Number & Err.Description, vbOKOnly)

End Select

End Sub

thanks in advance.....

mike,

Add a new line: Exit Sub
just before "ErrorHandler:"

Jim Cone
San Francisco, USA


"mike"
wrote in message
Problem: I want to create a backup of a workbook on open and feel there
are four possible error conditions to handle. Each should display a
different message to tell the user what is wrong.
1. disk full
2. disk write protected
3. file already open
4. file is read-only

I have an (partial) error handler designed but find that no matter
which condition actually exists always takes the Case Else path.
Sometimes err.number is "1004" while othertimes it is "0" (as seen in
the message box). code follows.

What am I doing wrong?

Sub Auto_open()
On Error GoTo ErrorHandler
If InStr(1, ThisWorkbook.Name, ".bck") < 1 Then
tmp = InStr(1, ThisWorkbook.Name, ".")
bckName = Left(ThisWorkbook.Name, tmp - 1) + ".bck"
ActiveWorkbook.SaveCopyAs (bckName)
End If
ErrorHandler:
Select Case Err.Number
Case 61
msg = "Disk full. Click Yes to continue without creating a backup."
msg = msg + "Otherwise click No to exit Excel."
response = MsgBox(msg, vbYesNo, "Warning: Disk Full")
If response = 6 Then Exit Sub
Application.Quit
Case Else
r = MsgBox("Error # :" & Err.Number & Err.Description, vbOKOnly)
End Select
End Sub

thanks in advance.....

Jim,
You are correct in your suggestion. This change,however, will only
ensure the error handler doesn't execute when there is no error. It
does not resolve the basic problem.

Search in the Excel VBA help file for "Trappable Errors".
Any of the listed error numbers can be added to Select Case statement
in your error handler.

Jim Cone


"mike"
wrote in message
oups.com

Jim,
You are correct in your suggestion. This change,however, will only
ensure the error handler doesn't execute when there is no error. It
does not resolve the basic problem.

Perhaps I didn't explain the problem well enough. The error handler is
ALWAYS served an error code of 1004 or zero no matter what the actual
error is. So, adding additional Case selections does not resolve the
problem. The problem is that the error trapped by the subroutine does
not return a value representative of, for example, a disk full
condition (code 61).

I did a search for "Error handling" which resulted in something like
1300+ threads. The ones I looked at did not help me.

look at the err object or even easier
download MZ-tools install them and add error trapping with a click.


--
Dnereb
------------------------------------------------------------------------
Dnereb's Profile: http://www.excelforum.com/member.php...o&userid=26182
View this thread: http://www.excelforum.com/showthread...hreadid=396815