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
| Dim TblChoix2(), choix1(), choix2()
--------------------------------------------------------------------------
Private Sub Label1_Click()
End Sub
-------------------------------------------------------------------------
Private Sub UserForm_Initialize()
choix2 = Application.Transpose(Range("paysfournisseurs"))
choix1 = Application.Transpose(Range("pays"))
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()
Condition = Me.ComboBox1
If Condition = "" Then Exit Sub
ligne = 0
ReDim TblChoix2(1 To UBound(choix2))
For i = LBound(choix2) To UBound(choix2)
If choix1(i) = Condition Then ligne = ligne + 1: TblChoix2(ligne) = choix2(i)
Next i
ReDim Preserve TblChoix2(1 To ligne)
Me.ComboBox2.List = TblChoix2
Me.ComboBox2.SetFocus
On Error Resume Next
SendKeys "{f4}"
End Sub
--------------------------------------------------------------
Private Sub ComboBox2_Change()
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
End Sub
----------------------------------------------------------
Private Sub CommandButton1_Click()
ActiveCell = Me.ComboBox1
ActiveCell.Offset(, 1) = Me.ComboBox2
Unload Me
End Sub |
Partager