Option Explicit
Dim TS As ListObject
Dim premlig As Byte
Dim encours As Boolean

Private Sub LblFermer_Click()
Unload Me
End Sub

Private Sub TextBoxNomUsage_Change()
    With Me
            If .TextBoxNomUsage = vbNullString Then
                .LV_Renouvellement.ListItems.Clear
'                .TextBoxTypeDemande = vbNullSring
                .TextBoxNoContrat = vbNullString
                .TextBoxGreta = vbNullString
            End If
        
        .TextBoxNomUsage.Value = UCase(TextBoxNomUsage.Value)
    End With
End Sub

Private Sub BtnRechercherRenouv_Click()

Dim c As Range
Dim prem As String

Me.LV_Renouvellement.ListItems.Clear

Set c = TS.ListColumns(17).DataBodyRange.Find("*" & TextBoxNomUsage & "*", LookIn:=xlValues)

If Not c Is Nothing Then
    prem = c.Adress
    Do
    
        With LV_Renouvellement
            .ListItems.Add , , TS.DataBodyRange(c.Row - premlig, 17).Value
            .ListItems(.ListItems.Count).ListSubItems.Add , , TS.DataBodyRange(c.Row - premlig, 18).Value
            .ListItems(.ListItems.Count).ListSubItems.Add , , TS.DataBodyRange(c.Row - premlig, 19).Value
            .ListItems(.ListItems.Count).ListSubItems.Add , , TS.DataBodyRange(c.Row - premlig, 35).Value
            
        End With
        
        Set c = TS.ListColumns(17).DataBodyRange.FindNext(c)
    Loop While Not c Is Nothing And c.Address <> prem
    
    End If
End Sub


'Mise en place initialisation
Private Sub UserForm_Initialize()

Set TS = Feuil1.ListObjects(1)
premlig = TS.HeaderRowRange.Row


Call majTableRecherche

'-------------Start Userform Centered inside Excel Screen (for dual monitors)-------------------
  
  With Me
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * Me.Height)
        
        .BtnRechercherRenouv.Default = True
    End With

End Sub


' ------------Réinitialise la liste de résultat de la recherche------------------
    Sub majTableRecherche()

        With Me.LV_Renouvellement

        .ColumnHeaders.Add , , TS.HeaderRowRange(17).Value, 80 'Nom d'usage
        .ColumnHeaders.Add , , TS.HeaderRowRange(18).Value, 80  'Prénom
        .ColumnHeaders.Add , , TS.HeaderRowRange(19).Value, 80 'Date de naissance
        .ColumnHeaders.Add , , TS.HeaderRowRange(23).Value, 80  'Date de début
        .ColumnHeaders.Add , , TS.HeaderRowRange(24).Value, 80  'Date de fin
        .ColumnHeaders.Add , , TS.HeaderRowRange(35).Value, 80  'Numéro Contrat


          .View = lvwReport
          .Gridlines = True
          .AllowColumnReorder = True
          .FullRowSelect = True
        End With
    End Sub
    

'---------------Valider sélection & récupérer numéro du contrat--------------
Private Sub LV_Renouvellement_ItemClick(ByVal Item As MSComctlLib.ListItem)

Dim ContratLgn As Integer

    With Me
        encours = True
        .TextBoxGreta = vbNullString
        
        .TextBoxTypeDemande = "RENOUVELLEMENT"
        .TextBoxNoContrat.Value = LV_Renouvellement.SelectedItem.ListSubItems(3).Text
        
        ContratLgn = TS.ListColumns(35).DataBodyRange.Find(.TextBoxNoContrat, LookIn:=xlValues, lookat:=xlWhole).Row - premlig
        
        encours = False
        
        .TextBoxGreta = TS.DataBodyRange(ContratLgn, 1)
        
    End With
    
End Sub
    


'Moteur de recherche - Alimentation de la listview

Private Sub LblMAJ_Click()

'Alimenter la copie du formulaire vers la BDD
 Worksheets("Form").Activate
     L = 2
        While Not IsEmpty(Cells(L, 1))
        
        L = L + 1
        Wend
    
    Cells(L, 1) = TextBoxGreta.Text
    Cells(L, 2) = TextBoxTypeContrat.Text
    Cells(L, 3) = TextBoxFonctionsLiées.Text
    Cells(L, 4) = TextBoxFonction.Text
    Cells(L, 5) = TextBoxFonction.Text
    Cells(L, 6) = TextBoxAgence.Text
    Cells(L, 7) = TextBoxCategorie.Text
    Cells(L, 8) = TextBoxEchelon.Text
    Cells(L, 9) = TextBoxINM.Text
    Cells(L, 10) = TextBoxIB.Text
    Cells(L, 11) = TextBoxQuotité.Text
    Cells(L, 12) = TextBoxNbreHeures.Text
    Cells(L, 14) = TextBoxOpportunité.Text
    Cells(L, 15) = TextBoxCivilité.Text
    Cells(L, 16) = TextBoxPatronymique.Text
    Cells(L, 17) = TextBoxUsage.Text
    Cells(L, 18) = TextBoxPrenom.Text
    Cells(L, 19) = Format(DateBox1.Value, "mm/dd/yyyy")
    Cells(L, 20) = TextBoxNationalité.Text
    Cells(L, 21) = TextBoxAdresse.Text
    Cells(L, 22) = TextBoxNivFormation.Text
    Cells(L, 23) = Format(DateBox3.Value, "mm/dd/yyyy")
    Cells(L, 24) = Format(DateBox4.Value, "mm/dd/yyyy")
    Cells(L, 26) = TextBoxRQTH.Text
    Cells(L, 27) = Format(DateBox2.Value, "mm/dd/yyyy")
    Cells(L, 28) = TextBoxNCA.Text
    Cells(L, 36) = TextBoxDemande.Text
    

End Sub