Bonjour,
Je vous joint mon code que j'aimerais améliorer, sans doublon et ligne vide dans le combobox2.
Malgrés plusieurs tentatives je n'y arrive pas. (débutant de chez débutant )
Ceci est un code réalisé grace à Mr Boisgontier
Merci d'avance
Je vous joint mon code que j'aimerais améliorer, sans doublon et ligne vide dans le combobox2.
Malgrés plusieurs tentatives je n'y arrive pas. (débutant de chez débutant )
Code:
Dim Tbl(), a, b
Private Sub UserForm_Initialize()
a = Range("article").Value
b = Range("SHIP_SHIP").Value
Set d1 = CreateObject("Scripting.Dictionary")
For Each c In b
d1(c) = ""
Next c
Me.ComboBox1.List = d1.keys
End Sub
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex = -1 Then
Set d1 = CreateObject("Scripting.Dictionary")
tmp = UCase(Me.ComboBox1) & "*"
For Each c In b
If UCase(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
ligne = 0
ReDim Tbl(1 To UBound(a))
For i = LBound(a) To UBound(a)
If b(i, 1) = Condition Then ligne = ligne + 1: Tbl(ligne) = a(i, 1)
Next i
ReDim Preserve Tbl(1 To ligne)
Me.ComboBox2.List = Tbl
Me.ComboBox2.SetFocus
If Val(Application.Version) > 10 Then SendKeys "{f4}"
End If
End Sub
Private Sub ComboBox2_Change()
If Me.ComboBox2.ListIndex = -1 Then
Set d1 = CreateObject("Scripting.Dictionary")
tmp = UCase(Me.ComboBox2) & "*"
For Each c In Tbl
If UCase(c) Like tmp Then d1(c) = ""
Next c
Me.ComboBox2.List = d1.keys
Me.ComboBox2.DropDown
Else
tmp1 = Me.ComboBox1: tmp2 = Me.ComboBox2
For p = 1 To UBound(a)
If b(p, 1) = tmp1 And a(p, 1) = tmp2 Then Me.TextBox1 = Range("référence")(p)
Next p
End If
End Sub
Private Sub CommandButton1_Click()
If Me.ComboBox1 <> "" And Me.ComboBox2 <> "" Then
ActiveCell = Me.ComboBox1
ActiveCell.Offset(, 1) = Me.ComboBox2
ActiveCell.Offset(, 2) = Me.TextBox1
Unload Me
Range("A4").Select
Else
MsgBox "Incomplet!"
Exit Sub
End If
End Sub
Merci d'avance