Private dl As Integer 'déclare la variable dl (Dernière Ligne)
Private pl As Range 'déclare la varaible pl (PLage)
Private Sub UserForm_Initialize()
With Sheets("Sheet1")
dl = .Cells(Application.Rows.Count, 1).End(xlUp).Row
Set pl = .Range("A4:A" & dl)
End With
Me.ctrl1.List = pl.Value
End Sub
Private Sub ctrl1_Change()
Dim i As Byte
Lg = ctrl1.ListIndex + 4
For i = 1 To 3
Me.Controls("ctrl" & i).Value = Sheets("Sheet1").Cells(Lg, Me.Controls("ctrl" & i).Tag)
Next i
End Sub
Private Sub Btn_OK_Click()
Dim r As Range
Dim li As Integer
Dim i As Integer
Set r = pl.Find(ctrl1.Value, , xlValues, xlWhole)
If r Is Nothing Then
li = dl + 1
Else
li = r.Row
If MsgBox(" Vous confirmez la modification ?", vbOKCancel + vbQuestion, "MODIFICATION DEMANDE EXISTANTE") = vbCancel Then Exit Sub
End If
For i = 1 To 3
Sheets("sheet1").Range(Me.Controls("Ctrl" & i).Tag & li).Value = Me.Controls("Ctrl" & i).Value
Next i
Unload Me
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub