Dim Tbl As Variant, NomFeuil As String
Private Sub CheckBox1_Click()
If CheckBox1 Then
NomFeuil = "EFFET_EMIS"
IniCbo "F"
End If
End Sub
Private Sub CheckBox2_Click()
If CheckBox2 Then
NomFeuil = "leasing"
IniCbo "G"
End If
End Sub
Private Sub ComboBox1_Change()
If ComboBox1 <> "" And ComboBox2 <> "" And CheckBox1 <> "" And CheckBox2 <> "" Then
CommandButton1.Enabled = True
End If
End Sub
Private Sub CommandButton1_Click()
Dim L As Long, Li As Long, ModeP As String
If Me.ComboBox1 <> "" And Me.ComboBox2 <> "" Then
If NomFeuil = "leasing" Then
ModeP = "Prélevement"
Else
ModeP = "Effet"
End If
With Worksheets("banque")
Li = .Range("A5000").End(xlUp).Row
For L = 1 To UBound(Tbl)
If Year(Tbl(L, 2)) = Val(Me.ComboBox2.Value) Then
If Month(Tbl(L, 2)) = Val(Me.ComboBox1.Value) Then
Li = Li + 1
.Cells(Li, 1) = CDate(Tbl(L, 2)) 'date
.Cells(Li, 2) = ModeP
.Cells(Li, 3) = Tbl(L, 1) 'ref
.Cells(Li, 4) = Tbl(L, 4) 'libellé
.Cells(Li, 7) = CDbl(Tbl(L, UBound(Tbl, 2))) 'débit
End If
End If
Next L
End With
End If
CommandButton1.Enabled = False
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub ComboBox2_Change()
Dim MonDico As Object, L As Long
If ComboBox2.Value <> "" Then
Me.ComboBox1.Clear
'sans doublons
Set MonDico = CreateObject("Scripting.Dictionary")
For L = 1 To UBound(Tbl)
If Year(Tbl(L, 2)) = Val(Me.ComboBox2.Value) Then
If Not MonDico.Exists(Month(Tbl(L, 2))) Then MonDico.Add Month(Tbl(L, 2)), Month(Tbl(L, 2))
End If
Next
Me.ComboBox1.List = MonDico.items
End If
End Sub
Sub IniCbo(LetCol As String)
Dim MonDico As Object, DerL As Long, L As Long
Me.ComboBox2.Clear
With Worksheets(NomFeuil)
DerL = .Range("A5000").End(xlUp).Row
.Range(.Cells(3, 1), .Cells(DerL, LetCol)).Sort Key1:=.Range("B4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=5, MatchCase:=True, Orientation:=xlTopToBottom
Tbl = .Range(.Cells(4, 1), .Cells(DerL, LetCol))
Set MonDico = CreateObject("Scripting.Dictionary")
For L = 1 To UBound(Tbl)
If Not MonDico.Exists(Year(Tbl(L, 2))) Then MonDico.Add Year(Tbl(L, 2)), Year(Tbl(L, 2))
Next
End With
Me.ComboBox2.List = MonDico.items
End Sub
Private Sub UserForm_Initialize()
CommandButton1.Enabled = False
End Sub
