Remove all items in Listbox when variable changes

[RockandGrohl]

Hello all,

I have a Userform with 3 listboxes. One is a product listbox and the other two are dates.


When you choose a product, the date listboxes automatically fill up with unique individual dates. You would then choose a date from one of the two date listboxes and continue your work.

I've found an issue, if I choose one product, the date listbox fills up as expected, but then if you choose another product, the date listbox is appended instead of replaced.

Below is the code where this takes place and I've marked the place where I think the dates should be deleted:

Sheets.Add.Name = "Temp"Set Temp = Worksheets("Temp")


Temp.Activate
Range("A1").Activate
F.Activate
Range("A3").Activate


[B]    ' DELETE DATES HERE[/B]


    Do Until Cells(ActiveCell.Row, "A").Value = ""
    If Cells(ActiveCell.Row, "A").Value = Prod Then
        OBD = Cells(ActiveCell.Row, "M").Value      '!@!
        IBD = Cells(ActiveCell.Row, "R").Value      '!@!
    
    Temp.Activate
    Cells(ActiveCell.Row, "A") = OBD
    Cells(ActiveCell.Row, "B") = IBD
    ActiveCell.Offset(1, 0).Activate
    F.Activate
    End If
    ActiveCell.Offset(1, 0).Activate
    Loop
Temp.Activate


Range("A:A").RemoveDuplicates (1)
Range("B:B").RemoveDuplicates (1)


    End With






Range("A1").Activate


' Fill Date Lists


Do Until Cells(ActiveCell.Row, "A").Value = ""
    Flight_CP.OBDateDD.AddItem Format(Cells(ActiveCell.Row, "A").Value, "dd/mm/yyyy")
    ActiveCell.Offset(1, 0).Activate
    Loop
    
Range("B1").Activate
Do Until Cells(ActiveCell.Row, "B").Value = ""
    Flight_CP.IBDateDD.AddItem Format(Cells(ActiveCell.Row, "B").Value, "dd/mm/yyyy")
    ActiveCell.Offset(1, 0).Activate
    Loop




F.Activate


Application.DisplayAlerts = False
Temp.Delete

To note, OBD and IBD are the variables that are placed into the two dates (stands for Outbound Date and Inbound Date)


So if I select the Jersey product, it will fill 4 unique Inbound Dates. Then if I select a Grand Prix product, it will see that there are 3 individual dates and add them to the bottom of the list, instead of removing the existing dates first.

The dates are added to a "Temp" sheet which is created, filled, sorted, de-duplicated. The dates are then added to the listbox and the Temp sheet is deleted.


Thanks.

 

[Fluff]

You can clear a listbox like

Me.ListBox1.Clear

 

[RockandGrohl]

You can clear a listbox like

Me.ListBox1.Clear

Ahh! See I was looking into things like Listbox.RemoveItem but then I wasn't sure how to get it to remove all. Clear is great, thank you.



EDIT: Super, this works:

Flight_CP.OBDateDD.Clear
Flight_CP.IBDateDD.Clear


Many thanks, as ever.

 

[Fluff]

You're welcome & thanks for the feedback