récapitulatif mensuel dans un userform

Private Sub UserForm_Initialize()
Dim data As Collection
Dim data1 As Collection
Dim c As Range
Dim i As Byte

Set data = New Collection
Set data1 = New Collection

For Each c In Range('a2:a' & Range('a65536').End(xlUp).Row)
        On Error Resume Next
        data.Add MonthName(Month(c)), CStr(Month(c))
        data1.Add Year(c), CStr(Year(c))
        On Error GoTo 0
Next c

For i = 1 To data.Count
        ListBox1.AddItem data(i)
Next i

For i = 1 To data1.Count
        ListBox2.AddItem data1(i)
Next i

        With Me.ListBox3
        .AddItem 'CARREFOUR'
        .AddItem 'AUCHAN'
        End With
        With Me.ListBox4
        .AddItem 'ANANAS'
        .AddItem 'POIRES'
        .AddItem 'POMMES'
        End With
End Sub

Cliquez pour agrandir...

Private Sub CommandButton1_Click()
Dim i As Integer
Dim tablo As Variant
Dim cpt As Integer, pal As Integer
Dim tonne As Double

tablo = Range('a2:e' & Range('a65536').End(xlUp).Row)

For i = 1 To 4
        If Controls('listbox' & i).ListIndex = -1 Then
                MsgBox 'merci de renseigner toutes les listes.'
                Exit Sub
        End If
Next i

For i = 1 To UBound(tablo)
        If MonthName(Month(tablo(i, 1))) = ListBox1 And _
        Year(tablo(i, 1)) = Val(ListBox2) And _
        tablo(i, 2) = ListBox4 And _
        tablo(i, 3) = ListBox3 Then
                cpt = cpt + 1
                pal = pal + tablo(i, 4)
                tonne = tonne + tablo(i, 5)
        End If
Next i

TextBox1 = ListBox3
TextBox2 = ListBox4
TextBox3 = cpt
TextBox4 = pal
TextBox5 = tonne
End Sub

Cliquez pour agrandir...