Vérification dans un formulaire

Private Sub enreg_Click()
Dim lr As ListRow
Dim Rep
  If IsDate(empr) = True Then
        Range("a1") = empr
    Else
        MsgBox "Format de date incorrect !" & Chr(10) & "La saisie doit être de type jj/mm/aaaa"
        empr.Value = ""
        empr.SetFocus
        Exit Sub
    End If

    If IsDate(retour) = True Then
        Range("a1") = retour
    Else
        MsgBox "Format de date incorrect !" & Chr(10) & "La saisie doit être de type jj/mm/aaaa"
        retour.Value = ""
        retour.SetFocus
        Exit Sub
    End If
    
    Set lr = Feuil5.ListObjects(1).ListRows.Add(1)
    lr.Range(1, 1) = Me.nom
    lr.Range(1, 2) = Me.prenom
    lr.Range(1, 3) = Me.service
    lr.Range(1, 4) = Me.designation
    lr.Range(1, 5) = Me.constructeur
    lr.Range(1, 6) = Me.modele
    lr.Range(1, 7) = Me.numeroate
    lr.Range(1, 8) = DateValue(Me.empr)
    lr.Range(1, 9) = DateValue(Me.retour)
    lr.Range(1, 10) = Me.zone

    ThisWorkbook.Save
      
    Rep = MsgBox("Saisir un autre emprunt pour le même utilisateur ?", vbYesNo)
    If Rep = vbYes Then
      RAZ
    Else
        Unload Me
    End If
        
End Sub

Private Sub CommandButton2_Click()
Dim ws As Worksheet, WO As Worksheet, DerLig As Long, DerFiltre As Long
Dim c As Range, d As Range, Plage As Range


If TextBox5 = "" Then Exit Sub
Set WO = Worksheets("Formulaire de prêt")
For Each ws In Worksheets
    If ws.Name Like Str_Type & "*" Then
        With ws
            Set c = .Columns(1).Find(TextBox5)
            If Not c Is Nothing Then Exit For
        End With
    End If
Next ws

If c Is Nothing Then
    MsgBox "Référence non trouvée": Exit Sub
Else
    'MsgBox "référence ok"
    '** verifie que le materiel est disponible
    DerLig = WO.Range("G" & Rows.Count).End(xlUp).Row - 1
    
    Set d = WO.Columns(7).Find(TextBox5)
    If Not d Is Nothing Then
        Set Plage = WO.Range("A3:L" & DerLig)
        Plage.Sort Key1:=Range("G3"), Order1:=xlAscending, Key2:=Range _
        ("I3"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
        DataOption2:=xlSortNormal

        WO.Range("A5").AutoFilter Field:=7, Criteria1:=TextBox5
        DerFiltre = Plage.SpecialCells(xlVisible).Columns(5).Find("*", , , , xlByColumns, xlPrevious).Row
        'MsgBox DerLig
        WO.ShowAllData

        If WO.Cells(DerFiltre, 11) = "" Then
            MsgBox "Ce matériel est en cours d'emprunt"
            TextBox5.Value = ""
            TextBox5.SetFocus
            Exit Sub
        End If
    '** fin verif
    End If
    Me.designation = c.Offset(0, 3)
    Me.constructeur = c.Offset(0, 2)
    Me.modele = c.Offset(0, 4)
    Me.numeroate = c.Offset(0, 0)
    
End If
End Sub