CODEWITHSUNDEEP
vbaexcel
Jeanjean
Jeanjean

Reputation: 863

VBA - Remove items from combobox

I'm struggling with an Unspecified Error I get while trying to remove duplicated items I have in my combobox...

I first add items using the RowSource property. Then I'd like to delete duplicated values using the RemoveItem property.

The error I get is generated at this line : CompteCOMBO.RemoveItem (j)

        With CompteCOMBO
            .Visible = True
            .RowSource = "Missions!ComptesExistant"
        End With

            For i = 0 To CompteCOMBO.ListCount - 1
               Valeur = CompteCOMBO.List(i)
                For j = i + 1 To CompteCOMBO.ListCount - 1
                    If Valeur = CompteCOMBO.List(j) Then
                        CompteCOMBO.RemoveItem (j) 'ERROR HERE
                    End If
                Next j
            Next i

Thanks for you help in advance.

Have a good day,

Jean

Upvotes: 0

Views: 5001

Answers (1)

CallumDA
CallumDA

Reputation: 12113

Here are a few suspect items:

  1. Iterating through and deleting at the same time generally needs a lot of care. You can easily end up with errors because you are trying to work with something that has been deleted.

  2. Be careful with this line For j = i + 1 To CompteCOMBO.ListCount - 1. It will eventually evaluate to something like For j = 5 to 4.

  3. It shouldn't be causing issues in this case, but don't use brackets here: CompteCOMBO.RemoveItem (j)

Instead of removing the duplicates after they are in the combobox, I'd filter out the duplicates first. Use a Dictionary, which has a handy Exists function. To use it you need to add a reference to Microsoft Scripting Runtime. Find it in Tools > References. Something like this would work:

Module 1

Sub ShowUserform()
    With New UserForm1
        .Show vbModal
    End With
End Sub

UserForm1

Private Sub UserForm_Initialize()
    Dim arr As Variant, v As Variant
    Dim d As Scripting.Dictionary

    Set d = New Scripting.Dictionary 
    arr = Array("a", "b", "a") 'or similarly:
    'arr = Application.Transpose(ThisWorkbook.Worksheets("Missions").Range("CompetesExistant")) 

    For Each v In arr
        If Not d.Exists(v) Then d.Add v, v
    Next v

    Me.ComboBox1.List = d.Keys
End Sub

Upvotes: 3

Related Questions