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
| 'MISE A JOUR DES VALEURS DEPUIS LA COMBOBOX1
Private Sub ComboBox1_Click()
Dim Ligne As Long
Dim Plage As Range, Est, Add As String, vLi As Integer, Vcol As Byte
If Me.ComboBox1.ListIndex = -1 Then Exit Sub 'ON sort si pas de sélection
Ligne = Evaluate("min(if(A1:A1000=""" & Me.ComboBox1.Value & """,row(A1:A1000)))")
ComboBox2 = Ws.Range("B" & Ligne) 'On alimente les données correspondant à la ligne
'MIN(IF((A2:A10="ABO")*(B2:B10="P")>0,ROW(A2:A10)))
[L1].FormulaArray = "=min(if((A1:A1000=""" & Me.ComboBox1.Value & """)*(B1:B1000=""" & Me.ComboBox2.Value & """)>0,row(A1:A1000)))"
ComboBox3 = Ws.Range("C" & Ligne) 'de l'index de la Combobox + 2 pour la ligne de Feuille
With ListBox1
.Clear
.ColumnCount = 6
.ColumnWidths = "110;100;20;250;80;30"
Set Plage = Range("A2:A" & [A65000].End(xlUp).Row)
Set Est = Plage.Find(ComboBox1)
If Not Est Is Nothing Then
Add = Est.Address
Do
.AddItem Cells(Est.Row, 1)
For Vcol = 2 To 6
.List(vLi, Vcol - 1) = Cells(Est.Row, Vcol)
Next
vLi = vLi + 1
Set Est = Plage.FindNext(Est)
Loop While Not Est Is Nothing And Est.Address <> Add
End If
TextBox1 = .ListCount
End With
End Sub
'MISE A JOUR DES VALEURS DEPUIS LA COMBOBOX2
Private Sub ComboBox2_Click()
Dim Plage As Range, Est, Add As String, vLi As Integer, Vcol As Byte
If Me.ComboBox2.ListIndex = -1 Then Exit Sub 'ON sort si pas de sélection
ComboBox3 = Ws.Range("C" & Me.ComboBox2.ListIndex + 3) 'On alimente les données correspondant à la ligne
With ListBox1
.Clear
.ColumnCount = 6
.ColumnWidths = "110;100;20;250;80;30"
Set Plage = Range("B2:B" & [B65000].End(xlUp).Row)
Set Est = Plage.Find(ComboBox2)
If Not Est Is Nothing Then
Add = Est.Address
Do
.AddItem Cells(Est.Row, 1)
For Vcol = 2 To 6
.List(vLi, Vcol - 1) = Cells(Est.Row, Vcol)
Next
vLi = vLi + 1
Set Est = Plage.FindNext(Est)
Loop While Not Est Is Nothing And Est.Address <> Add
End If
TextBox1 = .ListCount
End With
End Sub |
Partager