on error always running. even when it works

[id107]

Hi,

I've made a form which users will input data into which then writes to another file which then save and closes.

There is obviously an issue if more than one person presses submit at the same time, so I tried to add an error handler. The only problem I have is that it's giving me my error message box every time. Even when the write is successful.

Any ideas how I can get this to work?

error bit:

On Error GoTo ErrorHandler
 
'code
ErrorHandler:
MsgBox "File is in use. Please try again."
Exit Sub
    
End Sub

Full code:

Private Sub CommandButton21_Click()
On Error GoTo ErrorHandler
'Turn off updating
    Application.ScreenUpdating = False
'Declare the variables
    Dim FileLoc As String, FileName As String, AdvisorName As String, TheDate As String, RowCount As Long, _
    AdvisorEIN As String, Outcome As String, Cat As String, flg As Boolean
'Set the file information
    FileLoc = "K:\Glasgow\Aberdeen150\Call Drivers\"
    FileName = "calldrivers.xls"
'Get the advisor name
    AdvisorEIN = Environ("username")
    AdvisorName = Evaluate("=vlookup(" & AdvisorEIN & ",EIN!A:B,2,0)")
 
'Get the date
    TheDate = Now()
 
'Set the category
    Cat = "PSTN"
'Error checking
    For i = 1 To 19
    If Me.Controls("OptionButton" & i).Value = True Then
    flg = True
    Exit For
    End If
    Next
    If Not flg Then
    Application.ScreenUpdating = True
    MsgBox "Please choose an outcome"
    Exit Sub
 
End If
'Set the outcome
    If OptionButton1 = True Then
    Outcome = "Appt not required"
    End If
 
    If OptionButton2 = True Then
    Outcome = "BT.com - No order"
    End If
 
    If OptionButton3 = True Then
    Outcome = "BT.com - Only PSTN on order"
    End If
 
    If OptionButton4 = True Then
    Outcome = "BT.com - Wrong package/Equipment"
    End If
 
    If OptionButton5 = True Then
    Outcome = "CAD/Appt Change"
    End If
 
    If OptionButton6 = True Then
    Outcome = "Checking/Chasing order - Post CAD"
    End If
 
    If OptionButton7 = True Then
    Outcome = "Checking/Chasing order - Pre CAD"
    End If
 
    If OptionButton8 = True Then
    Outcome = "Connection Charge Offer - Cancel/Reissue"
    End If
 
    If OptionButton9 = True Then
    Outcome = "Connection Charge Offer - Enquiry/Dispute"
    End If
 
    If OptionButton10 = True Then
    Outcome = "Failed Homemove Order"
    End If
 
    If OptionButton11 = True Then
    Outcome = "Incomplete Order"
    End If
 
    If OptionButton12 = True Then
    Outcome = "Missed Appointment - Customer"
    End If
 
    If OptionButton13 = True Then
    Outcome = "Missed Appointment - Engineer No Show"
    End If
 
    If OptionButton14 = True Then
    Outcome = "Order Placed - Provide"
    End If
 
    If OptionButton15 = True Then
    Outcome = "Order Placed - Sim/Split"
    End If
 
    If OptionButton16 = True Then
    Outcome = "Order Placed - Start"
    End If
 
    If OptionButton17 = True Then
    Outcome = "Order Placed - WLT"
    End If
 
    If OptionButton18 = True Then
    Outcome = "Pay Deposit and Progress Order"
    End If
 
    If OptionButton19 = True Then
    Outcome = "SMS Received (Appointment/Activatiob)"
    End If
'***********************************************
'
'               Write the Data
'
'***********************************************
'Open the book and select the sheet
    Workbooks.Open FileName:=FileLoc & FileName
    Windows(FileName).Activate
    Sheets("Data").Select
'Find where we're writing to
    RowCount = Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
'Input the data
    Cells(RowCount, 1) = AdvisorName
    Cells(RowCount, 2) = AdvisorEIN
    Cells(RowCount, 3) = TheDate
    Cells(RowCount, 4) = Cat
    Cells(RowCount, 5) = Outcome
 
'Save and close the datafile
    Windows(FileName).Activate
    ActiveWindow.Close SaveChanges:=True
'Turn on updating
    Application.ScreenUpdating = True
 
'Close the input window
    Unload Me
ErrorHandler:
MsgBox "File is in use. Please try again."
Exit Sub
 
End Sub

 

[RoryA]

You need an Exit Sub line before the ErrorHandler: label.

 

[id107]

ooops.

thanks very much.