bonjour à tous,
j ai un soucis de combobox en cascade ! Pour les 3 premieres combo pas de soucis la 4eme ne marche pas si quelqu'un vois d ou cela peux venir
voici le code
Dim f
Private Sub UserForm_Initialize()
Set f = Sheets("BD")
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range(f.[A2], f.[A65000].End(xlUp))
Mondico(C.Value) = C.Value
Next C
Me.ComboBox1.List = Mondico.items
End Sub
Private Sub ComboBox1_Change()
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range(f.[A2], f.[A65000].End(xlUp))
If C = Me.ComboBox1 Then Mondico(C.Offset(, 1).Value) = C.Offset(, 1).Value
Next C
Me.ComboBox2.List = Mondico.items
Me.ComboBox2.ListIndex = -1
Me.ComboBox3.ListIndex = -1
Me.combobox4.ListIndex = -1
End Sub
Private Sub ComboBox2_Change()
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range(f.[A2], f.[A65000].End(xlUp))
If C = Me.ComboBox1 And C.Offset(, 1) = Me.ComboBox2 Then Mondico(C.Offset(, 2).Value) = C.Offset(, 2).Value
Next C
Me.ComboBox3.List = Mondico.items
Me.ComboBox3.ListIndex = -1
Me.combobox4.ListIndex = -1
End Sub
Private Sub ComboBox3_Change()
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range(f.[A2], f.[A65000].End(xlUp))
If C = Me.ComboBox2 And C.Offset(, 1) = Me.ComboBox3 Then Mondico(C.Offset(, 3).Value) = C.Offset(, 2).Value
Next C
Me.combobox4.List = Mondico.items
Me.combobox4.ListIndex = -1
End Sub
Private Sub ComboBox4_Change()
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range(f.[A2], f.[A65000].End(xlUp))
If C = Me.ComboBox3 And C.Offset(, 1) = Me.combobox4 Then Mondico(C.Offset(, 4).Value) = C.Offset(, 2).Value
Next C
End Sub
Private Sub CommandButton1_Click()
num = Sheets("Reception").Range("A65536").End(xlUp).Row + 1
Sheets("Reception").Activate
Range("A" & num).Value = (Date_Réception)
Range("B" & num).Value = ComboBox1
Range("C" & num).Value = ComboBox3
Range("D" & num).Value = ComboBox2
Range("E" & num).Value = CDbl(Qtes_recues)
Range("F" & num).Value = combobox4
Range("G" & num).Value = Fournisseur
Range("H" & num).Value = REf_Fournisseur
Range("I" & num).Value = CDbl(Prix_HT)
Range("K" & num).Value = CDbl(Remise)
Range("M" & num).Value = CDbl(TVA)
Exit Sub
End Sub
je vous en remercie a l'avance
j ai un soucis de combobox en cascade ! Pour les 3 premieres combo pas de soucis la 4eme ne marche pas si quelqu'un vois d ou cela peux venir
voici le code
Dim f
Private Sub UserForm_Initialize()
Set f = Sheets("BD")
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range(f.[A2], f.[A65000].End(xlUp))
Mondico(C.Value) = C.Value
Next C
Me.ComboBox1.List = Mondico.items
End Sub
Private Sub ComboBox1_Change()
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range(f.[A2], f.[A65000].End(xlUp))
If C = Me.ComboBox1 Then Mondico(C.Offset(, 1).Value) = C.Offset(, 1).Value
Next C
Me.ComboBox2.List = Mondico.items
Me.ComboBox2.ListIndex = -1
Me.ComboBox3.ListIndex = -1
Me.combobox4.ListIndex = -1
End Sub
Private Sub ComboBox2_Change()
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range(f.[A2], f.[A65000].End(xlUp))
If C = Me.ComboBox1 And C.Offset(, 1) = Me.ComboBox2 Then Mondico(C.Offset(, 2).Value) = C.Offset(, 2).Value
Next C
Me.ComboBox3.List = Mondico.items
Me.ComboBox3.ListIndex = -1
Me.combobox4.ListIndex = -1
End Sub
Private Sub ComboBox3_Change()
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range(f.[A2], f.[A65000].End(xlUp))
If C = Me.ComboBox2 And C.Offset(, 1) = Me.ComboBox3 Then Mondico(C.Offset(, 3).Value) = C.Offset(, 2).Value
Next C
Me.combobox4.List = Mondico.items
Me.combobox4.ListIndex = -1
End Sub
Private Sub ComboBox4_Change()
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range(f.[A2], f.[A65000].End(xlUp))
If C = Me.ComboBox3 And C.Offset(, 1) = Me.combobox4 Then Mondico(C.Offset(, 4).Value) = C.Offset(, 2).Value
Next C
End Sub
Private Sub CommandButton1_Click()
num = Sheets("Reception").Range("A65536").End(xlUp).Row + 1
Sheets("Reception").Activate
Range("A" & num).Value = (Date_Réception)
Range("B" & num).Value = ComboBox1
Range("C" & num).Value = ComboBox3
Range("D" & num).Value = ComboBox2
Range("E" & num).Value = CDbl(Qtes_recues)
Range("F" & num).Value = combobox4
Range("G" & num).Value = Fournisseur
Range("H" & num).Value = REf_Fournisseur
Range("I" & num).Value = CDbl(Prix_HT)
Range("K" & num).Value = CDbl(Remise)
Range("M" & num).Value = CDbl(TVA)
Exit Sub
End Sub
je vous en remercie a l'avance