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

Private Sub BtnFermer_Click()
Unload Me
End Sub

Private Sub TextBoxNomUsage_Change()

With Me
    If .TextBoxNomUsage = vbNullString Then
        .ListViewRésultats.ListItems.Clear
        .TextBoxTypeDemande = vbNullString
        .TextBoxNoContrat = vbNullString
        .TextBoxGreta = vbNullString
    End If
    .TextBoxNomUsage.Value = UCase(TextBoxNomUsage.Value)

End With
End Sub

Private Sub BtnRecherche_Click() 'Moteur de Recherche - Alimentation de la listView
Dim c As Range
Dim prem As String

Me.ListViewRésultats.ListItems.Clear

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

If Not c Is Nothing Then
    prem = c.Address
    Do
        With ListViewRésultats
            .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

Private Sub UserForm_Initialize()

'Mise en place initialisation
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)

    .BtnRecherche.Default = True
End With
End Sub

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

With Me.ListViewRésultats
    '.ColumnHeaders.Clear
    '.ListItems.Clear
    
    .ColumnHeaders.Add , , TS.HeaderRowRange(17).Value, 70 'Nom d'usage
    .ColumnHeaders.Add , , TS.HeaderRowRange(18).Value, 70 'Prénom
    .ColumnHeaders.Add , , TS.HeaderRowRange(19).Value, 70 'Date de naissance
    .ColumnHeaders.Add , , TS.HeaderRowRange(35).Value, 70 'Numéro Contrat

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

End Sub

Private Sub ListViewRésultats_ItemClick(ByVal Item As MSComctlLib.ListItem) 'Valider sélection & récupérer numéro du contrat
Dim ContratLgn as integer

With Me
    encours = True
    .TextBoxGreta = vbNullString 'vider la textbox
    
    .TextBoxTypeDemande = "AVENANT" 'Alimenter les autres Box
    .TextBoxNoContrat.Value = ListViewRésultats.SelectedItem.ListSubItems(3).Text 'reprendre N°contrat

    ContratLgn = TS.ListColumns(35).DataBodyRange.Find(.TextBoxNoContrat, LookIn:=xlValues, lookat:=xlWhole).Row - premlig 'N° de ligne N° Contrat
    
    encours = False
    
    .TextBoxGreta = TS.DataBodyRange(ContratLgn, 1)
End With

End Sub

Private Sub TextBoxGreta_Change()

If encours = True Then Exit Sub

If MsgBox("Y a t il eu des avenants pour ce contrat ?", vbYesNo + vbDefaultButton2 + vbQuestion, "Historique des avenants du contrat") = vbNo Then
    NouveauxAvtsUF.Show
Else:
    With HistoAvtUF
        .TextBoxNoContrat.Value = AvenantsUF.TextBoxNoContrat.Value
        .Show
    End With
End If

End Sub