Multiple ListBox selection avoiding duplicates VBA

Question

I am building a Userform which contains two ListBoxes such that the user can select options from ListBox1 and add them to ListBox2 or alternatively remove options from ListBox2

What I am struggling with is how can I prevent the duplicates from being added to the ListBox2? Essentially, I want to build in a function (?) which checks if an option is already included in ListBox2

Private Sub CommandButton3_Click()

'### Adds Items from ListBox1 to ListBox2
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i)
Next i

ListBox1.Selected

End Sub

Private Sub CommandButton4_Click()

'### Removes Items from ListBox2
Dim counter As Integer
counter = 0

For i = 0 To ListBox2.ListCount - 1
    If ListBox2.Selected(i - counter) Then
        ListBox2.RemoveItem (i - counter)
        counter = counter + 1
    End If
Next i

End Sub

Not sure how contains function would work in VBA. However, I have found another similar thread on stackoverflow which solves a similar problem and working to adjust it to my needs: stackoverflow.com/questions/19755920/… — cybujan
– cybujan, Commented Jan 23, 2015 at 13:53

cybujan · Accepted Answer · 2015-01-23 17:25:47Z

The code below worked as a solution to the problem:

Private Sub CommandButton3_Click()

For i = 0 To ListBox1.ListCount - 1

    If ListBox1.Selected(i) = True Then
        valCheck (ListBox1.List(i))
    End If

Next i

End Sub

Private Function valCheck(str As String)

'### Adds Items from ListBox1 to ListBox2

Dim valExists As Boolean

    valExists = False


        For i = 0 To ListBox2.ListCount - 1

            If ListBox2.List(i) = str Then
                valExists = True
            End If

        Next i

        If valExists Then
            MsgBox ("already exists")
        Else
            ListBox2.AddItem str
        End If





End Function
Private Sub CommandButton4_Click()

'### Removes Items from ListBox2

For i = 0 To ListBox2.ListCount - 1

    If ListBox2.Selected(i) = True Then ListBox2.RemoveItem (i)

Next i



End Sub

Collectives™ on Stack Overflow

Multiple ListBox selection avoiding duplicates VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related