VBA Listbox Question - Don't add duplicate items

[Scott Huish]

So, I'm building a form like this:

First column is lstBrandsAvailable, 2nd column is lstBrandsSelected

If I click Add, I don't want it to add it if it has already been selected.

This is what I have now but I don't know what kind of loop to do to check and see if it's lstBrandsSelected yet or not.

Private Sub cmdAdd_Click()
Set available = UserForm1.lstBrandsAvailable: Set selectedbrand = UserForm1.lstBrandsSelected
selectedbrand.AddItem available.Value
End Sub

I have tried to search for answers for this but I am stuck, nothing quite seems to fit exactly what I'm trying to do.

 

[John_w]

Try this routine:

Private Sub Copy_Items_Between_ListBoxes(lb1 As MSForms.ListBox, lb2 As MSForms.ListBox)

    Dim i1 As Long, i2 As Long
    Dim found As Boolean

    For i1 = 0 To lb1.ListCount - 1
        If lb1.Selected(i1) Then
            found = False
            For i2 = 0 To lb2.ListCount - 1
                If CStr(lb2.List(i2, 0)) = CStr(lb1.List(i1, 0)) Then
                    'Found this lb1 column 1 item in lb2 column 1
                    found = True
                End If
            Next
            If Not found Then
                'Copy item from lb1 to lb2
                lb2.AddItem lb1.List(i1, 0)
            End If
            'Deselect
            lb1.Selected(i1) = False
        End If
    Next

End Sub

and call it like this:

Private Sub cmdAdd_Click()
    Copy_Items_Between_ListBoxes Me.lstBrandsAvailable, Me.lstBrandsSelected
End Sub

 

[RoryA]

You could also actually remove it from the first list when you add it so it can't be selected again.

 

[Scott Huish]

Sorry, I didn't think of this when I asked the question, the values in the first listbox are 2 columns, Is it a big deal to modify the code to copy both columns?

RoryA: That didn't work for me. I think its because I'm using RowSource to populate the ListBox