Error Handler

SaraO · Mar 8th 2016

Hello,

I have a code that I use to retrieve work orders into the original template. The user enters the job number they wish to retrieve, very simple and works really good. However I need to add an error handler so if they enter a job number that does not exist that it displays a message telling the user that the job number they are trying to retrieve does not exist.

This is the code I have...

Code

Private Sub cmdRetrieveMiscWO_Click()
Dim wb As Workbook
Application.ScreenUpdating = False
Set wb = Workbooks.Open("C:[URL="file://\\General\Work"]\Work[/URL] Orders\Miscellaneous\JB-" & Me.txtJobNo & ".xlsx", True, True)
With ThisWorkbook.Worksheets("MiscWO")
    .Range("E5:F5").Value = wb.Worksheets(1).Range("E5:F5").Value
    .Range("A7:G7").Value = wb.Worksheets(1).Range("A7:G7").Value
    .Range("A9:G9").Value = wb.Worksheets(1).Range("A9:G9").Value
    .Range("A11:G11").Value = wb.Worksheets(1).Range("A11:G11").Value
    .Range("A16:A18").Value = wb.Worksheets(1).Range("A16:A18").Value
    .Range("A22:G48").Value = wb.Worksheets(1).Range("A22:G48").Value
    .Range("D1").Value = wb.Worksheets(1).Range("D1").Value
    End With
    wb.Close False
    Set wb = Nothing
        
    Application.ScreenUpdating = True
    
    Unload Me
    
End Sub

Display More

Grimes0332 · Mar 8th 2016

Re: Error Handler

There's 2 sides to this.

Check the file exists BEFORE trying to open it - if not then inform user and exit
Trap any other errors that might occur.

Try sometihing like

Code

Private Sub cmdRetrieveMiscWO_Click()


   Dim wb               As Workbook


   On Error GoTo ErrHandler


   Application.ScreenUpdating = False


   If Dir("C:\Work Orders\Miscellaneous\JB-" & Me.txtJobNo & ".xlsx") = vbNullString Then
      MsgBox "The file " & "C:\Work Orders\Miscellaneous\JB-" & Me.txtJobNo & ".xlsx" & vbCrLf & "does not exist.", vbExclamation
   Else
      Set wb = Workbooks.Open("C:\Work Orders\Miscellaneous\JB-" & Me.txtJobNo & ".xlsx", True, True)


      With ThisWorkbook.Worksheets("MiscWO")
         .Range("E5:F5").Value = wb.Worksheets(1).Range("E5:F5").Value
         .Range("A7:G7").Value = wb.Worksheets(1).Range("A7:G7").Value
         .Range("A9:G9").Value = wb.Worksheets(1).Range("A9:G9").Value
         .Range("A11:G11").Value = wb.Worksheets(1).Range("A11:G11").Value
         .Range("A16:A18").Value = wb.Worksheets(1).Range("A16:A18").Value
         .Range("A22:G48").Value = wb.Worksheets(1).Range("A22:G48").Value
         .Range("D1").Value = wb.Worksheets(1).Range("D1").Value
      End With
   End If


ErrHandler:


   If Err.Number <> 0 Then
      MsgBox "Error - " & Err.Description, vbExclamation, "Error"
   End If
   
   If Not wb Is Nothing Then
      wb.Close False
   End If
   
   Set wb = Nothing
   
   Application.ScreenUpdating = True
   Unload Me


End Sub

Display More

shknbk2 · Mar 8th 2016

Re: Error Handler

Added a check to see if Dir returns a filename. If not, the messagebox shows and the Sub exits without closing the form.

Code

Private Sub cmdRetrieveMiscWO_Click()
Dim wb As Workbook, fileExists As String


fileExists = Dir("C:\Work Orders\Miscellaneous\JB-" & Me.txtJobNo & ".xlsx")
If fileExists = "" Then
    MsgBox "The job number doesn't exist."
    Exit Sub
End If


Application.ScreenUpdating = False
Set wb = Workbooks.Open("C:\Work Orders\Miscellaneous\JB-" & Me.txtJobNo & ".xlsx", True, True)
With ThisWorkbook.Worksheets("MiscWO")
    .Range("E5:F5").Value = wb.Worksheets(1).Range("E5:F5").Value
    .Range("A7:G7").Value = wb.Worksheets(1).Range("A7:G7").Value
    .Range("A9:G9").Value = wb.Worksheets(1).Range("A9:G9").Value
    .Range("A11:G11").Value = wb.Worksheets(1).Range("A11:G11").Value
    .Range("A16:A18").Value = wb.Worksheets(1).Range("A16:A18").Value
    .Range("A22:G48").Value = wb.Worksheets(1).Range("A22:G48").Value
    .Range("D1").Value = wb.Worksheets(1).Range("D1").Value
    End With
    wb.Close False
    Set wb = Nothing
        
    Application.ScreenUpdating = True
    
    Unload Me
    
End Sub

Display More

Edit:
Oops, looks like an answer was already posted. Here's my code anyway.

SaraO · Mar 8th 2016

Re: Error Handler

Works great!

Thank you so much!!!

Error Handler

Participate now!

Share