VBA - Excel Listbox - Look for Duplicates when Adding Items to Second Listbox

Question

So I am new to the forms side of the VBA coding and I seem to be struggling a bit with this one.

What I have did was follow this tutorial:

https://www.excel-easy.com/vba/examples/multiple-list-box-selections.html

And I have adapted it in a way that suits my needs, but now I am having an issue or two that I do not understand how I can resolve.

The code in the tutorial adds two list boxes to a form and then the add button copy items from the first listbox to the second and the remove button removes items from the second listbox.

The problem is that you can add a specific item more than once, and considering I would like to use the values in the second listbox, this is a problem as I need only unique values.

The code below is what I have come up with so far, but I am getting an error:

Private Sub btn_Add_Filter_Click()

    For i = 0 To lbx_Filters_List.ListCount - 1

        If lbx_Filters_List.Selected(i) = True Then

            For X = 0 To lbx_Filters.ListCount

                If Not IsError(lbx_Filters.List(X)) Then

                    mVal = 0

                    If lbx_Filters.List(X) <> "" And lbx_Filters.List(X) = lbx_Filters_List.List(i) Then

                        myVal = 1

                    End If

                End If

                 If myVal = 0 Then

                    lbx_Filters.AddItem _
                    lbx_Filters_List.List(i)

                 End If

            Next X

        End If

    Next i

End Sub

The error occurs the second time I try and add the same item from the first listbox and what happens is that the second for loop will loop once and on the second loop it throws an error on this line:

If Not IsError(lbx_Filters.List(X)) Then

Error being:

Could not get the list property. Invalid property array index

hi. whats the value of X and lbx_Filters.List(X) when it throws the error ? — Luis Curado
@LuisCurado thank you for the reply. I am only now getting back to this problem. x=1at the time of execution and I cannot get a value for lbx_Filters.List(X)as that is throwing the error. — Eitel Dagnin
@GMalc Thank you for the reply and the link. I had come across this post, but the problem is that with this, the "list" needs to be completed already. What is going to happen is that the user will add the items from the first list box to the second and then might decide to remove one again. So as far as I can tell, the solution provided relies on a completed list/collection/array rather than dynamically updating as we go along. — Eitel Dagnin
can u check the length of lbx_Filters before the error. u can create a var with the value of lbx_Filters.ListCount — Luis Curado

Eitel Dagnin Eitel Dagnin · Accepted Answer · 2019-04-29T15:53:08

I eventually (with the help of the comments above) solved the issue. Thank you to all who assisted.

Private Sub btn_Add_Filter_Click()

    Dim Size As Integer
    Size = lbx_Filters.ListCount
    Dim ListBoxContents() As String
    Dim ListBoxC() As Variant

    Dim i As Integer, y As Integer, X As Integer, myVal As Integer, lItem As Integer

    myVal = 0

    For i = 0 To lbx_Filters_List.ListCount - 1

        If lbx_Filters_List.Selected(i) = True Then

            If Size > 0 Then

                For lItem = 0 To lbx_Filters.ListCount - 1

                    For X = 0 To lbx_Filters_List.ListCount - 1

                        If Not IsError(lbx_Filters_List.List(X)) And lbx_Filters.List(lItem) = lbx_Filters_List.List(i) Then

                            myVal = 1

                        End If

                    Next X

                Next lItem

            End If

            If myVal = 0 Then

                lbx_Filters.AddItem _
                lbx_Filters_List.List(i)

            End If

        End If

    Next i

End Sub

VBA - Excel Listbox - Look for Duplicates when Adding Items to Second Listbox

1 Answers