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 |
