Option Explicit
Private Sub UserForm_Initialize()
Dim WS As Worksheet
Dim Plage As Range, Cell As Range
Dim c As Byte, x As Integer, y As Integer
Set WS = ThisWorkbook.Worksheets("Feuil1")
Set Plage = WS.Range("A2:A" & WS.Range("A1000").End(xlUp).Row)
Me.ComboBox1 = "TOUT"
Me.ComboBox2 = "TOUT"
Me.ListBox1.ColumnCount = 7
For Each Cell In Plage
If Cell.Offset(0, 6) = "" Then 'Entrée Seule
With Me.ListBox1
.AddItem Cell
For c = 1 To 7
.Column(c, x) = Cell.Offset(0, c)
Next c
x = x + 1
End With
End If
Next Cell
Me.ComboBox1.List = Array("A", "B", "TOUT")
Me.ComboBox2.List = Array("CLS", "ACC", "TOUT") 'CLS="CC"+"DD"
End Sub
Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim Plage As Range, Cell As Range
Dim c As Byte, x As Integer, y As Integer
If Me.ComboBox1.ListIndex = -1 Then Exit Sub
If Me.ComboBox2.ListIndex = -1 Then Exit Sub
Me.ListBox1.Clear
Set WS = ThisWorkbook.Worksheets("Feuil1")
Set Plage = WS.Range("A2:A" & WS.Range("A1000").End(xlUp).Row)
For Each Cell In Plage
If Cell.Offset(0, 6) = "" Then 'Entrée Seule
If Me.ComboBox1.Value <> "TOUT" And Me.ComboBox2.Value <> "TOUT" Then
If Cell.Offset(0, 5) = ComboBox1.Value Then
If Cell.Offset(0, 4) = ComboBox2.Value Then
With Me.ListBox1
.AddItem Cell
For c = 1 To 7
.Column(c, x) = Cell.Offset(0, c)
Next c
x = x + 1
End With
End If
End If
ElseIf Me.ComboBox1.Value <> "TOUT" And Me.ComboBox2.Value = "TOUT" Then
If Cell.Offset(0, 5) = ComboBox1.Value Then
With Me.ListBox1
.AddItem Cell
For c = 1 To 7
.Column(c, x) = Cell.Offset(0, c)
Next c
x = x + 1
End With
End If
ElseIf Me.ComboBox1.Value = "TOUT" And Me.ComboBox2.Value <> "TOUT" Then
If Cell.Offset(0, 4) = ComboBox2.Value Then
With Me.ListBox1
.AddItem Cell
For c = 1 To 7
.Column(c, x) = Cell.Offset(0, c)
Next c
x = x + 1
End With
End If
ElseIf Me.ComboBox1.Value = "TOUT" And Me.ComboBox2.Value = "TOUT" Then
With Me.ListBox1
.AddItem Cell
For c = 1 To 7
.Column(c, x) = Cell.Offset(0, c)
Next c
x = x + 1
End With
End If
End If
Next Cell
End Sub
تحميل محتوى الموضوع
عرض نسخة صالحة للطباعة
أرسل هذا الموضوع إلى صديق
