Bonjour,
Saisie intuitive caractère par caractère sur 2 niveaux
Option Compare Text
Dim f, TblChoix2(), choix2(), choix1(), code()
Private Sub UserForm_Initialize()
Set f = Sheets("bd")
choix2 = Application.Transpose(f.Range("b2:b" & f.[b65000].End(xlUp).Row))
choix1 = Application.Transpose(f.Range("a2:a" & f.[a65000].End(xlUp).Row))
code = Application.Transpose(f.Range("g2:g" & f.[a65000].End(xlUp).Row))
Set d1 = CreateObject("Scripting.Dictionary")
For Each c In choix1: d1(c) = "": Next c
Me.ComboBox1.List = d1.keys
End Sub
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex = -1 And IsError(Application.Match(Me.ComboBox1, choix1, 0)) Then
Set d1 = CreateObject("Scripting.Dictionary")
tmp = Me.ComboBox1 & "*"
For Each c In choix1:
If c Like tmp Then d1(c) = ""
Next c
Me.ComboBox1.List = d1.keys
Me.ComboBox1.DropDown
Else
Condition = Me.ComboBox1
If Condition = "" Then Exit Sub
Set d2 = CreateObject("Scripting.Dictionary")
For i = LBound(choix2) To UBound(choix2)
If choix1(i) = Condition Then d2(choix2(i)) = ""
Next i
TblChoix2 = d2.keys
Me.ComboBox2.List = TblChoix2
Me.ComboBox2.SetFocus
If Val(Application.Version) > 10 Then SendKeys "{f4}"
End If
End Sub
Private Sub ComboBox2_Change()
If Me.ComboBox1 <> "" Then
If Me.ComboBox2.ListIndex = -1 And IsError(Application.Match(Me.ComboBox2, choix2, 0)) Then
Set d1 = CreateObject("Scripting.Dictionary")
tmp = UCase(Me.ComboBox2) & "*"
For Each c In TblChoix2
If UCase(c) Like tmp Then d1(c) = ""
Next c
Me.ComboBox2.List = d1.keys
Me.ComboBox2.DropDown
Else
For i = LBound(choix1) To UBound(choix2)
If choix1(i) = Me.ComboBox1 And choix2(i) = Me.ComboBox2 Then Me.TextBox1 = code(i)
Next i
End If
End If
End Sub
Ceuzin