Dim d As Object, dd As Object 'mémorise les variables
Private Sub ComboBox1_Change()
With ComboBox2
If ComboBox1.ListIndex = -1 Then .Text = "": .Clear: Exit Sub
.List = Split(Mid(d(ComboBox1.Text), 2), Chr(1))
If .ListCount = 1 Then .ListIndex = 0: Exit Sub
.Text = ""
.SetFocus
End With
CreateObject("wscript.shell").SendKeys "%{DOWN}" 'déroule la liste
End Sub
Private Sub Modifier_Click()
Dim lig&
If ComboBox1.ListIndex = -1 Then ComboBox1.DropDown: Exit Sub
If ComboBox2.ListIndex = -1 Then ComboBox2.DropDown: Exit Sub
If TextBox1 = "" Then TextBox1.SetFocus: Exit Sub
If TextBox2 = "" Then TextBox2.SetFocus: Exit Sub
lig = dd(ComboBox1 & Chr(1) & ComboBox2)
With Sheets("Feuil1")
.Cells(lig, 1) = TextBox1
.Cells(lig, 2) = TextBox2
End With
Unload Me
End Sub
Private Sub Annuler_Click()
Unload Me
End Sub
Private Sub UserForm_initialize()
Dim tablo, i&, x$, y$
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
Set dd = CreateObject("Scripting.Dictionary")
dd.CompareMode = vbTextCompare 'la casse est ignorée
tablo = Sheets("Feuil1").[A1].CurrentRegion.Resize(, 2) 'matrice, plus rapide
For i = 2 To UBound(tablo)
x = tablo(i, 1)
y = x & Chr(1) & tablo(i, 2)
d(x) = d(x) & Chr(1) & tablo(i, 2) 'mémorise le prénom
dd(y) = i 'mémorise la ligne
Next
ComboBox1.List = d.keys 'liste sans doublon
End Sub