wnazzaro
01-11-2007, 03:25 PM
In my code, I have a few On Error GoTo statements. I am having a problem I don't understand.
The user enters a file name via an InputBox and the code sets the file equal to a Worksheet object. In testing, I have entered the incorrect file name. The first time the error occurs, the handler works correctly. The handler allows the user to change their input with a GoTo statement to an earlier line label so they can input the data again. When I input the data again, but still not correct, I get a Run Time Error '9'. Why didn't my On Error statement work the second time? It's the exact same error.
I've attached two blank workbooks with the below code attached to one of them. To recreate the error, enter part of the name of the file "-Error.xls". Say Yes when it asks if you want to try and enter the name again, however don't fully correct the name. The error occurs at the line: Set MUL =.
All help or better ideas are greatly appreciated.
Bill
Here's the code:
Public Sub myIDhelp()
'What is the file name
Dim myID As String
EnterID:
myID = InputBox("Enter the myID", "myID", myID)
'Check that myID is in the correct format.
If Not InStr(myID, "-") = 1 Then
MsgBox "You did not enter the ID in the correct format." & vbCrLf & _
"The first character must be '-'" & vbCrLf & _
"Please correct the my ID.", vbCritical
GoTo EnterID:
End If
On Error GoTo MyHandler:
'Format data.
Dim CurBook As Workbook, CurSheet As Worksheet
Dim CurBookName As String
'The name of the download
For Each CurBook In Workbooks
If CurBook.Name = myID & ".XLS" Then
CurBookName = CurBook.Name
End If
Next CurBook
On Error GoTo WorksheetError:
Dim MUL As Workbook, MULname As String
Dim Sheet1 As Worksheet
MULname = UCase(myID) & "_Help.XLS"
Set MUL = Workbooks(MULname)
Set Sheet1 = MUL.Worksheets("Sheet1")
Set CurBook = Workbooks(CurBookName)
Set CurSheet = CurBook.Worksheets(1)
Exit Sub
WorksheetError:
MsgBox "Something is wrong with the myID you entered." & vbCrLf & _
"Make sure the myID you enter matches the beginning of the file name." _
& vbCrLf & "Including case."
Dim YN As Byte
YN = MsgBox("Do you want to try and re-enter the my ID?", _
vbYesNo, "my ID Error")
If YN = 6 Then
Err.Clear
GoTo EnterID:
Else
Application.ScreenUpdating = True
Application.StatusBar = False
Err.Clear
Exit Sub
End If
MyHandler:
MsgBox Err.Description & vbCrLf & _
"Send a mail to EDC support for follow-up"
Application.ScreenUpdating = True
Application.StatusBar = False
Err.Clear
End Sub
The user enters a file name via an InputBox and the code sets the file equal to a Worksheet object. In testing, I have entered the incorrect file name. The first time the error occurs, the handler works correctly. The handler allows the user to change their input with a GoTo statement to an earlier line label so they can input the data again. When I input the data again, but still not correct, I get a Run Time Error '9'. Why didn't my On Error statement work the second time? It's the exact same error.
I've attached two blank workbooks with the below code attached to one of them. To recreate the error, enter part of the name of the file "-Error.xls". Say Yes when it asks if you want to try and enter the name again, however don't fully correct the name. The error occurs at the line: Set MUL =.
All help or better ideas are greatly appreciated.
Bill
Here's the code:
Public Sub myIDhelp()
'What is the file name
Dim myID As String
EnterID:
myID = InputBox("Enter the myID", "myID", myID)
'Check that myID is in the correct format.
If Not InStr(myID, "-") = 1 Then
MsgBox "You did not enter the ID in the correct format." & vbCrLf & _
"The first character must be '-'" & vbCrLf & _
"Please correct the my ID.", vbCritical
GoTo EnterID:
End If
On Error GoTo MyHandler:
'Format data.
Dim CurBook As Workbook, CurSheet As Worksheet
Dim CurBookName As String
'The name of the download
For Each CurBook In Workbooks
If CurBook.Name = myID & ".XLS" Then
CurBookName = CurBook.Name
End If
Next CurBook
On Error GoTo WorksheetError:
Dim MUL As Workbook, MULname As String
Dim Sheet1 As Worksheet
MULname = UCase(myID) & "_Help.XLS"
Set MUL = Workbooks(MULname)
Set Sheet1 = MUL.Worksheets("Sheet1")
Set CurBook = Workbooks(CurBookName)
Set CurSheet = CurBook.Worksheets(1)
Exit Sub
WorksheetError:
MsgBox "Something is wrong with the myID you entered." & vbCrLf & _
"Make sure the myID you enter matches the beginning of the file name." _
& vbCrLf & "Including case."
Dim YN As Byte
YN = MsgBox("Do you want to try and re-enter the my ID?", _
vbYesNo, "my ID Error")
If YN = 6 Then
Err.Clear
GoTo EnterID:
Else
Application.ScreenUpdating = True
Application.StatusBar = False
Err.Clear
Exit Sub
End If
MyHandler:
MsgBox Err.Description & vbCrLf & _
"Send a mail to EDC support for follow-up"
Application.ScreenUpdating = True
Application.StatusBar = False
Err.Clear
End Sub