Dim lig As Integer
Dim stpevt As Boolean

Private Sub btnAjout_Click()

With Feuil1.ListObjects(1)

    With .DataBodyRange
        .Item(lig, 7) = cboTypeMarche.Value
        .Item(lig, 8) = cboChargeMission.Value
        .Item(lig, 9) = cboProcedure.Value
        .Item(lig, 11) = txtObjet.Value
        .Item(lig, 13) = txtAttributaire.Value
        .Item(lig, 14) = txtSiret.Value
        .Item(lig, 15) = txtlanceProcedure.Value
        .Item(lig, 16) = txtDateRemisePli.Value
        .Item(lig, 17) = txtNotifi.Value
        .Item(lig, 19) = cboNaturePrix.Value
        .Item(lig, 20) = txtEstimaBeoins.Value
        .Item(lig, 21) = txtMontantHT.Value
        .Item(lig, 23) = txtDataAvisCGEFI.Value
        .Item(lig, 24) = txtDateCAO.Value
        .Item(lig, 25) = txtNotifAR.Value
        .Item(lig, 26) = txtDateAvisAttrib.Value
        .Item(lig, 28) = cboCloture.Value
    End With
End With
End Sub

Private Sub btnEffacer_Click()
Dim ctrl As Control

stpevt = True

For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
        Case "TextBox"
            If UCase(ctrl.Name) <> "TXTDATE" Then ctrl.Value = vbNullString
        Case "Listbox", "ComboBox"
            ctrl.Value = ""
            ctrl.ListIndex = -1
    End Select
Next ctrl

btnAjout.Enabled = False: lig = 0
stpevt = False
Me.Height = 505
End Sub

Private Sub btnFermer_Click()
Unload Me
End Sub

Private Sub btnTableauSource_Click()
Call btnFermer_Click
Feuil1.Activate
End Sub

Private Sub btnRechercher_Click()

Me.Height = 650
TextBox2.SetFocus

End Sub

Private Sub ListBox1_Click()
Dim ctrl As Control
Dim lg As Integer
Dim prem
Dim c As Range

stpevt = True

For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
        Case "TextBox"
            If UCase(ctrl.Name) <> "TXTDATE" And UCase(ctrl.Name) <> "TEXTBOX2" Then ctrl.Value = vbNullString
        Case "Listbox", "ComboBox"
            If UCase(ctrl.Name) <> "LISTBOX1" And UCase(ctrl.Name) <> "LISTBOX2" Then
                ctrl.Value = ""
                ctrl.ListIndex = -1
            End If
    End Select
Next ctrl

lg = ListBox1.ListIndex 'index ligne sélectionnée dans listbox1

With Feuil1.ListObjects(1)
    Set c = .ListColumns(4).Range.Find(ListBox1.List(lg, 3), LookIn:=xlValues)
        
    If Not c Is Nothing Then
        prem = c.Address

        Do
            If ListBox1.List(lg, 4) = vbNullString Then lg = c.Row - 1: Exit Do
            If .DataBodyRange(c.Row - 1, 5) = CDbl(ListBox1.List(lg, 4)) Then lg = c.Row - 1: Exit Do
            Set c = .ListColumns(4).Range.FindNext(c)
        Loop While Not c Is Nothing And c.Address <> prem

    End If
End With
With Feuil1.ListObjects(1).DataBodyRange
    txtDate = .Item(lg, 1)
    txtAnnee = .Item(lg, 2)
    txtNumero = .Item(lg, 3)
    txtNumeroMarche = .Item(lg, 4)
    txtLot = .Item(lg, 5)
    txtNumeroLot = .Item(lg, 6)
    cboTypeMarche.Value = .Item(lg, 7)
    cboChargeMission.Value = .Item(lg, 8)
    cboProcedure.Value = .Item(lg, 9)
    txtObjet.Value = .Item(lg, 11)
    txtAttributaire.Value = .Item(lg, 13)
    txtSiret.Value = .Item(lg, 14)
    txtlanceProcedure.Value = .Item(lg, 15)
    txtDateRemisePli.Value = .Item(lg, 16)
    txtNotifi.Value = .Item(lg, 17)
    cboNaturePrix.Value = .Item(lg, 19)
    txtEstimaBeoins.Value = .Item(lg, 20)
    txtMontantHT.Value = .Item(lg, 21)
    txtDataAvisCGEFI.Value = .Item(lg, 23)
    txtDateCAO.Value = .Item(lg, 24)
    txtNotifAR.Value = .Item(lg, 25)
    txtDateAvisAttrib.Value = .Item(lg, 26)
    cboCloture.Value = .Item(lg, 28)
End With
btnAjout.Enabled = True: lig = lg
stpevt = False
End Sub

Private Sub TextBox2_Change() 'recherche du N° marché ou chargé de mission
Dim prem
Dim i As Byte, col As Byte
Dim c As Range

Me.ListBox1.Clear
If TextBox2 = vbNullString Then Exit Sub

If Me.TextBox2 <> "" And IsNumeric(TextBox2.Value) Then col = 4 Else: col = 8

    With Feuil1.ListObjects(1)
        Set c = .ListColumns(col).Range.Find("*" & Me.TextBox2.Value & "*", LookIn:=xlValues)
        
        If Not c Is Nothing Then
           prem = c.Address
           i = 0
           Do
               Me.ListBox1.AddItem
               Me.ListBox1.List(i, 0) = .DataBodyRange(c.Row - 1, 1) 'c.Offset(0, -3).Value 'date
               Me.ListBox1.List(i, 1) = .DataBodyRange(c.Row - 1, 2) 'c.Offset(0, -2).Value 'annee
               Me.ListBox1.List(i, 2) = .DataBodyRange(c.Row - 1, 3) 'c.Offset(0, -1).Value 'n°
               Me.ListBox1.List(i, 3) = .DataBodyRange(c.Row - 1, 4) 'c.Offset(0, 0).Value 'N° marche
               Me.ListBox1.List(i, 4) = .DataBodyRange(c.Row - 1, 5) 'c.Offset(0, 1).Value 'lot
               Me.ListBox1.List(i, 5) = .DataBodyRange(c.Row - 1, 6) 'c.Offset(0, 2).Value 'N°lotType lot
               Me.ListBox1.List(i, 6) = .DataBodyRange(c.Row - 1, 7) 'c.Offset(0, 3).Value 'Type lot
               Me.ListBox1.List(i, 7) = .DataBodyRange(c.Row - 1, 8) 'c.Offset(0, 4).Value 'charge mission
        
               Set c = .ListColumns(col).Range.FindNext(c)
               i = i + 1
           Loop While Not c Is Nothing And c.Address <> prem
        End If
    End With

Set c = Nothing
End Sub

Private Sub txtLot_Change()

If stpevt = True Then Exit Sub

With Feuil1.ListObjects(1)
    If txtLot = vbNullString Then
        txtAnnee = vbNullString
        txtNumero = vbNullString
        txtNumeroLot = vbNullString
        txtNumeroMarche = vbNullString
        btnAjout.Enabled = False
        Exit Sub
    End If
    
    If .ListRows.Count = 0 Then
        .ListRows.Add: lig = 1
    Else: .ListRows.Add: lig = .ListRows.Count
    End If
    With .DataBodyRange
        .Item(lig, 1) = Format(CDate(txtDate.Value), "mm/dd/yyyy")
        If txtLot > 0 Then .Item(lig, 5) = txtLot.Value
        
        txtAnnee.Value = .Item(lig, 2).Value
        txtNumero = .Item(lig, 3).Value
        txtNumeroMarche = .Item(lig, 4).Value
        txtNumeroLot = .Item(lig, 6).Value
        btnAjout.Enabled = True
    End With
    
    Call Verification
    
End With

End Sub

Private Sub UserForm_Initialize()

txtDate.Value = Format(Now, "DD/MM/YYYY")

With Feuil3
    cboTypeMarche.List = .ListObjects("TTypeMarche").DataBodyRange.Value
    cboChargeMission.List = .ListObjects("TChargeMisssion").DataBodyRange.Value
    cboProcedure.List = .ListObjects("TProcedure").DataBodyRange.Value
    cboCodeNCMP.List = .ListObjects("TCodeNCMP").ListColumns(1).DataBodyRange.Value
    cboMode.List = .ListObjects("Tmode").DataBodyRange.Value
    cboNaturePrix.List = .ListObjects("Tprix").DataBodyRange.Value
    cboCloture.List = .ListObjects("TCloture").DataBodyRange.Value
End With

With ListBox1
    .ColumnCount = 8
    .ColumnWidths = "50;50;20;80;20;50;70;100"
End With
With ListBox2
    .ColumnCount = 8
    .ColumnWidths = "50;50;20;80;20;50;70;100"
    .AddItem "Date"
    .List(0, 1) = "Année"
    .List(0, 2) = "N°"
    .List(0, 3) = "N° Marché"
    .List(0, 4) = "Lot"
    .List(0, 5) = "N° Lot"
    .List(0, 6) = "Type Marché"
    .List(0, 7) = "Chargé mission"
End With
'Me.ScrollBars = fmScrollBarsVertical
'Me.ScrollHeight = 850
Me.Height = 505 'dimensionner USF

End Sub

Private Sub Verification() 'vérifier si pas deux fois le même lot pour le même numéro de marche
Dim c As Range
Dim i As Byte

With Feuil1.ListObjects(1)
    Set c = .ListColumns(5).DataBodyRange.Find(txtLot, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            
            If c.Offset(0, -1).Value = txtNumeroMarche And c.Value = CDbl(txtLot) Then i = i + 1
                
            If i = 2 Then
                MsgBox "Ce lot existe déjà pour le numéro de marché " & txtNumeroMarche, vbCritical, "Doublon de lot"
                .ListRows(lig).Delete
                txtLot = vbNullString
                Exit Do
            End If
            Set c = .ListColumns(5).DataBodyRange.FindNext(c)
        Loop While Not c Is Nothing And firstaddress <> c.Address
    End If
End With
End Sub