1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
|
Option Compare Text
Dim TblChoix2(), TblChoix3(), choix2(), choix1(), choix3()
Private Sub UserForm_Initialize()
choix1 = Application.Transpose(Range("Nom"))
choix2 = Application.Transpose(Range("Prenom"))
choix3 = Application.Transpose(Range("Employeur"))
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 ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ComboBox1.ListIndex = -1 Then Me.ComboBox1 = Me.ComboBox1.List(0)
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
Set d3 = CreateObject("Scripting.Dictionary")
Condition1 = Me.ComboBox1
Condition2 = Me.ComboBox2
If Condition1 = "" Or Condition2 = "" Then Exit Sub
Set d3 = CreateObject("Scripting.Dictionary")
For i = LBound(choix3) To UBound(choix3)
If choix1(i) = Condition1 And choix2(i) = Condition2 Then d3(choix3(i)) = ""
Next i
TblChoix3 = d3.Keys
Me.ComboBox3.List = TblChoix3
Me.ComboBox3.SetFocus
If Val(Application.Version) > 10 Then SendKeys "{f4}"
End If
End If
End Sub
Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ComboBox2.ListIndex = -1 Then Me.ComboBox2 = Me.ComboBox2.List(0)
End Sub
Private Sub ComboBox3_Change()
If Me.ComboBox1 <> "" And Me.ComboBox2 <> "" Then
If Me.ComboBox3.ListIndex = -1 And IsError(Application.Match(Me.ComboBox3, choix3, 0)) Then
Set d1 = CreateObject("Scripting.Dictionary")
tmp = UCase(Me.ComboBox3) & "*"
For Each c In TblChoix3
If c Like tmp Then d1(c) = ""
Next c
Me.ComboBox3.List = d1.Keys
Me.ComboBox3.DropDown
Else
Condition1 = Me.ComboBox1
Condition2 = Me.ComboBox2
Condition3 = Me.ComboBox3
End If
End If
End Sub
Private Sub ComboBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ComboBox3.ListIndex = -1 Then Me.ComboBox3 = Me.ComboBox3.List(0)
End Sub
Private Sub CommandButton1_Click()
If Me.ComboBox1 <> "" And Me.ComboBox2 <> "" Then
ActiveCell = UCase(Me.ComboBox1)
ActiveCell.Offset(, 1) = Me.ComboBox2
ActiveCell.Offset(, 2) = Me.ComboBox3
Unload Me
Else
MsgBox "Incomplet!"
Exit Sub
End If
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub |
Partager