Private TEST As Boolean
Private O1 As worksheet ' en utilisant le bon objet , l'aide dynamique est plus fournie ' Object
Private PL As Range
Private Sub UserForm_Initialize()
Dim DL As long ' Peut éviter des problème si plus de 32000 lignes plutot qu' Integer
Dim PLD As Range
Dim D As Object
Dim CEL As Range
Set O1 = Sheets("Commande-Controle")
DL = O1.Cells(Application.Rows.Count, 1).End(xlUp).Row
Set PLD = O1.Range("A3:A" & DL)
Set PL = O1.Range("D3:I" & DL)
Set D = CreateObject("Scripting.Dictionary")
'For Each CEL In PLD
D(CEL.Value) = ""
'Next CEL
D.clear
Me.ComboBox1.List = D.keys
End Sub
Private Sub ComboBox1_Change()
Dim O2 As Object
Dim PLV As Range
Dim N As String
If TEST = True Then Exit Sub
TEST = True
Application.ScreenUpdating = False
Me.ComboBox1.Value = DateSerial(Year(Me.ComboBox1.Value), Month(Me.ComboBox1.Value), Day(Me.ComboBox1.Value))
N = Replace(Me.ComboBox1.Value, "/", "_")
On Error Resume Next
Set O2 = Sheets(N)
If Err = 0 Then MsgBox "Date déjà effectuée !": End
If Err <> 0 Then
Err.Clear
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = N
Set O2 = ActiveSheet 'définit l'onglet O2
End If
On Error GoTo 0
O1.Range("D2:I2").Copy O2.Range("A1").value
O1.ListObjects("RECAP").Range.AutoFilter Field:=1, Criteria1:=Me.ComboBox1.Value
Set PLV = PL.SpecialCells(xlCellTypeVisible)
PLV.Copy O2.Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0).value
O1.Range("A3").AutoFilter
Unload Me
O2.Select
Application.ScreenUpdating = True
End Sub