Bonsoir tout le monde
Voici un excellent code récupéré mais j'aimerais remplacer la listview par une listbox. Que faut-il rectifier?
Merci
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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