Bonjour le forum,
je charge ma listbox avec cette macro mais je n'arrive pas la charger sans doublons. auriez vous une solution
Sub listingdepuisle()
Dim TheDate As Date ' Déclare les variables.
Dim Msg
Dim c As Long
Windows("RECAP.xls").Activate
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
Range("A3").Select
reponse = InputBox("Entrez une date (jj/mm/aaaa)", "Listing contrôle depuis le... ", Sheets("DONNEES").Range("A1"))
If IsDate(reponse) Then
TheDate = reponse
UserForm13.Label2 = Date - DateDiff("d", TheDate, Now)
UserForm13.Caption = "Récapitulatif des contrôles depuis le :" & TheDate
UserForm13.CommandButton2.Caption = "Mise en page du récapitulatif des contrôles depuis le: " & UserForm13.Label2.Caption
lafin = Worksheets("DONNEES").Range("AV65536").End(xlUp).Row + 1
For n = 1 To lafin
If Worksheets("DONNEES").Range("AV" & n) = CDate(UserForm13.Label2) Then
debut = n
Exit For
End If
Next n
If n = lafin + 1 Then
MsgBox ("La date n'existe pas!")
Call listingdepuisle
Else
For n = debut To lafin
UserForm13.ListBox1.AddItem Worksheets("DONNEES").Range("a" & n).Text & " " & Worksheets("DONNEES").Range("b" & n).Text
Next n
For n = 1 To UserForm13.ListBox1.ListCount - 1
UserForm13.ListBox1.List(n - 1, 1) = Worksheets("DONNEES").Range("C" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 2) = Worksheets("DONNEES").Range("d" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 3) = Worksheets("DONNEES").Range("e" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 4) = Worksheets("DONNEES").Range("f" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 5) = Worksheets("DONNEES").Range("g" & n + debut - 1)
If IsNumeric(Worksheets("DONNEES").Range("i" & n + debut - 1)) Then
UserForm13.ListBox1.List(n - 1, 6) = CDbl(Worksheets("DONNEES").Range("i" & n + debut - 1))
Else
UserForm13.ListBox1.List(n - 1, 6) = Worksheets("DONNEES").Range("i" & n + debut - 1)
End If
'UserForm13.ListBox1.List(n - 1, 6) = Worksheets("DONNEES").Range("i" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 7) = Worksheets("DONNEES").Range("P" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 8) = Worksheets("DONNEES").Range("Q" & n + debut - 1)
Next n
UserForm13.Show
'UserForm13.CommandButton2_Click
End If
Else
If reponse = "" Then
Exit Sub
Else
MsgBox ("Format date incorrect!")
Call listingdepuisle
End If
End If
End Sub
merci d'avance
je charge ma listbox avec cette macro mais je n'arrive pas la charger sans doublons. auriez vous une solution
Sub listingdepuisle()
Dim TheDate As Date ' Déclare les variables.
Dim Msg
Dim c As Long
Windows("RECAP.xls").Activate
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
Range("A3").Select
reponse = InputBox("Entrez une date (jj/mm/aaaa)", "Listing contrôle depuis le... ", Sheets("DONNEES").Range("A1"))
If IsDate(reponse) Then
TheDate = reponse
UserForm13.Label2 = Date - DateDiff("d", TheDate, Now)
UserForm13.Caption = "Récapitulatif des contrôles depuis le :" & TheDate
UserForm13.CommandButton2.Caption = "Mise en page du récapitulatif des contrôles depuis le: " & UserForm13.Label2.Caption
lafin = Worksheets("DONNEES").Range("AV65536").End(xlUp).Row + 1
For n = 1 To lafin
If Worksheets("DONNEES").Range("AV" & n) = CDate(UserForm13.Label2) Then
debut = n
Exit For
End If
Next n
If n = lafin + 1 Then
MsgBox ("La date n'existe pas!")
Call listingdepuisle
Else
For n = debut To lafin
UserForm13.ListBox1.AddItem Worksheets("DONNEES").Range("a" & n).Text & " " & Worksheets("DONNEES").Range("b" & n).Text
Next n
For n = 1 To UserForm13.ListBox1.ListCount - 1
UserForm13.ListBox1.List(n - 1, 1) = Worksheets("DONNEES").Range("C" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 2) = Worksheets("DONNEES").Range("d" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 3) = Worksheets("DONNEES").Range("e" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 4) = Worksheets("DONNEES").Range("f" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 5) = Worksheets("DONNEES").Range("g" & n + debut - 1)
If IsNumeric(Worksheets("DONNEES").Range("i" & n + debut - 1)) Then
UserForm13.ListBox1.List(n - 1, 6) = CDbl(Worksheets("DONNEES").Range("i" & n + debut - 1))
Else
UserForm13.ListBox1.List(n - 1, 6) = Worksheets("DONNEES").Range("i" & n + debut - 1)
End If
'UserForm13.ListBox1.List(n - 1, 6) = Worksheets("DONNEES").Range("i" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 7) = Worksheets("DONNEES").Range("P" & n + debut - 1)
UserForm13.ListBox1.List(n - 1, 8) = Worksheets("DONNEES").Range("Q" & n + debut - 1)
Next n
UserForm13.Show
'UserForm13.CommandButton2_Click
End If
Else
If reponse = "" Then
Exit Sub
Else
MsgBox ("Format date incorrect!")
Call listingdepuisle
End If
End If
End Sub
merci d'avance