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 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147
| Option Explicit
Dim TabTemp As Variant
Dim DerLgn As Integer
Dim L As Integer
Dim X As Integer
Dim I As Integer
Dim C As Byte
Dim ligne As Integer
Private Sub CheckBox1_Click()
If UserForm1.CheckBox1.Value = True Then Call AjoutItem
End Sub
Private Sub cmbAgence_Change()
If UserForm1.CheckBox1.Value = True Then
Call AjoutItem
Exit Sub
End If
If UserForm1.cmbAgence.Value = "" Then Exit Sub
' Met à vide la liste des mois
UserForm1.cmbMois.Value = ""
' Si & non coché, construit la liste selon l'AGENCE
With Me.ListView1
.ListItems.Clear
With .ColumnHeaders
.Clear
.Add , , "Date", 50
.Add , , "Agence", 70
.Add , , "Client", 95
.Add , , "Achat", 50
End With
.FullRowSelect = True
.Gridlines = True
.LabelEdit = 1
.ListItems.Clear
.View = lvwReport
With ThisWorkbook.Worksheets("BD")
.Activate
DerLgn = .Range("A65535").End(xlUp).Row
TabTemp = .Range(.Cells(2, 1), .Cells(DerLgn, 4)).Value
.Range("A1").Sort Key1:=.Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
X = 1
For L = 1 To UBound(TabTemp, 1)
If TabTemp(L, 2) = Me.cmbAgence.Value Then
.ListItems.Add , , TabTemp(L, 1)
.ListItems(X).ListSubItems.Add , , TabTemp(L, 2)
.ListItems(X).ListSubItems.Add , , TabTemp(L, 3)
.ListItems(X).ListSubItems.Add , , TabTemp(L, 4)
X = X + 1
End If
Next
End With
'TOTAL
With Me.txtTotal
Me.txtTotal = ListView1.ListItems.Count - 0
End With
End Sub
Private Sub cmbMois_Change()
If UserForm1.CheckBox1.Value = True Then
Call AjoutItem
Exit Sub
End If
If UserForm1.cmbMois.Value = "" Then Exit Sub
UserForm1.cmbAgence.Value = ""
' Si & non coché, construit la liste selon le MOIS
With Me.ListView1
.ListItems.Clear
With .ColumnHeaders
.Clear
.Add , , "Date", 50
.Add , , "Agence", 70
.Add , , "Client", 95
.Add , , "Achat", 50
End With
.FullRowSelect = True
.Gridlines = True
.LabelEdit = 1
.ListItems.Clear
.View = lvwReport
With ThisWorkbook.Worksheets("BD")
.Activate
DerLgn = .Range("A65535").End(xlUp).Row
TabTemp = .Range(.Cells(2, 1), .Cells(DerLgn, 4)).Value
.Range("A1").Sort Key1:=.Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
X = 1
For L = 1 To UBound(TabTemp, 1)
If Format(CDate(TabTemp(L, 1)), "mmmm") = Me.cmbMois.Value Then
.ListItems.Add , , TabTemp(L, 1)
.ListItems(X).ListSubItems.Add , , TabTemp(L, 2)
.ListItems(X).ListSubItems.Add , , TabTemp(L, 3)
.ListItems(X).ListSubItems.Add , , TabTemp(L, 4)
X = X + 1
End If
Next L
End With
'TOTAL
With Me.txtTotal
Me.txtTotal = ListView1.ListItems.Count - 0
End With
End Sub
Sub AjoutItem()
With Me.ListView1
.ListItems.Clear
With .ColumnHeaders
.Clear
.Add , , "Date", 50
.Add , , "Agence", 70
.Add , , "Client", 95
.Add , , "Achat", 50
End With
.FullRowSelect = True
.Gridlines = True
.LabelEdit = 1
.ListItems.Clear
.View = lvwReport
With ThisWorkbook.Worksheets("BD")
.Activate
DerLgn = .Range("A65535").End(xlUp).Row
TabTemp = .Range(.Cells(2, 1), .Cells(DerLgn, 4)).Value
.Range("A1").Sort Key1:=.Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
X = 1
For L = 1 To UBound(TabTemp, 1)
If TabTemp(L, 2) = Me.cmbAgence.Value Then
If Format(CDate(TabTemp(L, 1)), "mmmm") = Me.cmbMois.Value Then
.ListItems.Add , , TabTemp(L, 1)
.ListItems(X).ListSubItems.Add , , TabTemp(L, 2)
.ListItems(X).ListSubItems.Add , , TabTemp(L, 3)
.ListItems(X).ListSubItems.Add , , TabTemp(L, 4)
X = X + 1
End If
End If
Next L
End With
End Sub
Private Sub UserForm_initialize()
cmbAgence.RowSource = "Liste!A2: A10"
cmbMois.RowSource = "Liste!B2: B13"
End Sub |
Partager