+ Reply to Thread
Results 1 to 5 of 5

avoiding duplicates in listbox (added from another listbox)

  1. #1
    KR
    Guest

    avoiding duplicates in listbox (added from another listbox)

    I have a userform with a list of source names, a second list box in which to
    put selected names, and an add button to move the selected names to the
    second listbox.

    I need to avoid having the same name added more than once, and although the
    code below seems like a good idea to prevent duplicates, I'm getting a type
    mismatch. I think I'm mis-referencing the second element in the match
    statement, but I'm not sure how to reference the list inside the second
    listbox.

    Please help? I'm using XL2003

    Thanks,
    keith

    For i = 0 To frmEntry.lstSource.ListCount - 1
    If frmEntry.lstSource.Selected(i) = True Then
    If IsError(Application.Match(frmEntry.lstSource.List(i),
    frmEntry.lstSelected.List, 0)) Then '*** mismatch error
    frmEntry.lstSelected.AddItem frmEntry.lstSource.List(i)
    End If
    End If
    Next i


    --
    The enclosed questions or comments are entirely mine and don't represent the
    thoughts, views, or policy of my employer. Any errors or omissions are my
    own.



  2. #2
    Dave Peterson
    Guest

    Re: avoiding duplicates in listbox (added from another listbox)

    How about deleting them from the first listbox when you add them to the second.
    It might be easier to code and more intuitive for the user.

    This may give you an idea:
    http://groups.google.com/groups?selm...BDD4%40msn.com

    KR wrote:
    >
    > I have a userform with a list of source names, a second list box in which to
    > put selected names, and an add button to move the selected names to the
    > second listbox.
    >
    > I need to avoid having the same name added more than once, and although the
    > code below seems like a good idea to prevent duplicates, I'm getting a type
    > mismatch. I think I'm mis-referencing the second element in the match
    > statement, but I'm not sure how to reference the list inside the second
    > listbox.
    >
    > Please help? I'm using XL2003
    >
    > Thanks,
    > keith
    >
    > For i = 0 To frmEntry.lstSource.ListCount - 1
    > If frmEntry.lstSource.Selected(i) = True Then
    > If IsError(Application.Match(frmEntry.lstSource.List(i),
    > frmEntry.lstSelected.List, 0)) Then '*** mismatch error
    > frmEntry.lstSelected.AddItem frmEntry.lstSource.List(i)
    > End If
    > End If
    > Next i
    >
    > --
    > The enclosed questions or comments are entirely mine and don't represent the
    > thoughts, views, or policy of my employer. Any errors or omissions are my
    > own.


    --

    Dave Peterson

  3. #3
    KR
    Guest

    Re: avoiding duplicates in listbox (added from another listbox)

    There are three source lists that can populate the source listbox (changed
    by clicking on one of three cmdbuttons), as the user needs to add names from
    more than one of the lists. I've kept the lists separate because the
    grouping is important (for example, they may need to pick one name from
    /each/ list for a total of three unique names, but some names may be on 2 or
    even all 3 lists).

    So they may look at source list 1, add one (or more) names, then click to
    see the second source list, add one (or more) names, etc. Since they can't
    use the same person more than once, it is more reliable to programatically
    prevent repeats rather than rely on the users to avoid duplicates.

    Should I just add the names to an array in memory instead of trying to match
    against the destination listbox? I can do that, I just thought there might
    be a more elegant way to compare directly to the array that is already in
    the listbox.

    FWIW, the example referenced in the URL is even more elegant than what I'm
    doing, as I'm not removing items from the source list (e.g. select with
    replacement) because otherwise I'd have to keep track of each list even when
    it wasn't showing (user switches lists, then switches back) and also which
    list(s) the duplicates names are on - I'd have to check all three source
    lists to remove a name that was selected, then know which list(s) to put the
    names back on if it was unselected.

    Thanks,
    Keith

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > How about deleting them from the first listbox when you add them to the

    second.
    > It might be easier to code and more intuitive for the user.
    >
    > This may give you an idea:
    > http://groups.google.com/groups?selm...BDD4%40msn.com
    >
    > KR wrote:
    > >
    > > I have a userform with a list of source names, a second list box in

    which to
    > > put selected names, and an add button to move the selected names to the
    > > second listbox.
    > >
    > > I need to avoid having the same name added more than once, and although

    the
    > > code below seems like a good idea to prevent duplicates, I'm getting a

    type
    > > mismatch. I think I'm mis-referencing the second element in the match
    > > statement, but I'm not sure how to reference the list inside the second
    > > listbox.
    > >
    > > Please help? I'm using XL2003
    > >
    > > Thanks,
    > > keith
    > >
    > > For i = 0 To frmEntry.lstSource.ListCount - 1
    > > If frmEntry.lstSource.Selected(i) = True Then
    > > If IsError(Application.Match(frmEntry.lstSource.List(i),
    > > frmEntry.lstSelected.List, 0)) Then '*** mismatch error
    > > frmEntry.lstSelected.AddItem frmEntry.lstSource.List(i)
    > > End If
    > > End If
    > > Next i
    > >
    > > --
    > > The enclosed questions or comments are entirely mine and don't represent

    the
    > > thoughts, views, or policy of my employer. Any errors or omissions are

    my
    > > own.

    >
    > --
    >
    > Dave Peterson




  4. #4
    Dave Peterson
    Guest

    Re: avoiding duplicates in listbox (added from another listbox)

    I built a userform with 3 Input Listboxes (listbox1, listbox2, listbox3)
    and one "output" listbox (listbox4)

    And 4 commandbuttons (cancel, Process Listbox1, process listbox2, and process
    listbox3):

    This was the code I used to test it:

    Option Explicit
    Private Sub CommandButton1_Click()
    Unload Me
    End Sub
    Private Sub CommandButton2_Click()
    Call DoTheWork(Me.ListBox1)
    End Sub
    Private Sub CommandButton3_Click()
    Call DoTheWork(Me.ListBox2)
    End Sub
    Private Sub CommandButton4_Click()
    Call DoTheWork(Me.ListBox3)
    End Sub
    Private Sub UserForm_Initialize()
    Dim iCtr As Long
    Dim lCtr As Long

    'some test data
    For lCtr = 1 To 3
    With Me.Controls("Listbox" & lCtr)
    .MultiSelect = fmMultiSelectMulti
    For iCtr = 1 To 5
    .AddItem "asdf" & Format(iCtr, "00")
    Next iCtr
    End With
    Next lCtr

    For iCtr = 1 To 3
    Me.Controls("Commandbutton" & iCtr + 1).Caption _
    = "Process ListBox " & iCtr
    Next iCtr
    End Sub
    Sub DoTheWork(myListBox As Control)

    Dim iCtr As Long
    Dim jCtr As Long
    Dim res As Variant
    Dim myArr As Variant

    If myListBox.ListCount > 0 Then
    For iCtr = 0 To myListBox.ListCount - 1
    If myListBox.Selected(iCtr) Then
    If Me.ListBox4.ListCount = 0 Then
    Me.ListBox4.AddItem myListBox.List(iCtr)
    Else
    ReDim myArr(0 To Me.ListBox4.ListCount - 1)
    For jCtr = 0 To Me.ListBox4.ListCount - 1
    myArr(jCtr) = Me.ListBox4.List(jCtr, 0)
    Next jCtr
    res = Application.Match(myListBox.List(iCtr), myArr, 0)
    If IsError(res) Then
    'add it
    Me.ListBox4.AddItem myListBox.List(iCtr)
    Else
    Beep
    End If
    End If
    End If
    Next iCtr
    End If
    End Sub


    KR wrote:
    >
    > There are three source lists that can populate the source listbox (changed
    > by clicking on one of three cmdbuttons), as the user needs to add names from
    > more than one of the lists. I've kept the lists separate because the
    > grouping is important (for example, they may need to pick one name from
    > /each/ list for a total of three unique names, but some names may be on 2 or
    > even all 3 lists).
    >
    > So they may look at source list 1, add one (or more) names, then click to
    > see the second source list, add one (or more) names, etc. Since they can't
    > use the same person more than once, it is more reliable to programatically
    > prevent repeats rather than rely on the users to avoid duplicates.
    >
    > Should I just add the names to an array in memory instead of trying to match
    > against the destination listbox? I can do that, I just thought there might
    > be a more elegant way to compare directly to the array that is already in
    > the listbox.
    >
    > FWIW, the example referenced in the URL is even more elegant than what I'm
    > doing, as I'm not removing items from the source list (e.g. select with
    > replacement) because otherwise I'd have to keep track of each list even when
    > it wasn't showing (user switches lists, then switches back) and also which
    > list(s) the duplicates names are on - I'd have to check all three source
    > lists to remove a name that was selected, then know which list(s) to put the
    > names back on if it was unselected.
    >
    > Thanks,
    > Keith
    >
    > "Dave Peterson" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about deleting them from the first listbox when you add them to the

    > second.
    > > It might be easier to code and more intuitive for the user.
    > >
    > > This may give you an idea:
    > > http://groups.google.com/groups?selm...BDD4%40msn.com
    > >
    > > KR wrote:
    > > >
    > > > I have a userform with a list of source names, a second list box in

    > which to
    > > > put selected names, and an add button to move the selected names to the
    > > > second listbox.
    > > >
    > > > I need to avoid having the same name added more than once, and although

    > the
    > > > code below seems like a good idea to prevent duplicates, I'm getting a

    > type
    > > > mismatch. I think I'm mis-referencing the second element in the match
    > > > statement, but I'm not sure how to reference the list inside the second
    > > > listbox.
    > > >
    > > > Please help? I'm using XL2003
    > > >
    > > > Thanks,
    > > > keith
    > > >
    > > > For i = 0 To frmEntry.lstSource.ListCount - 1
    > > > If frmEntry.lstSource.Selected(i) = True Then
    > > > If IsError(Application.Match(frmEntry.lstSource.List(i),
    > > > frmEntry.lstSelected.List, 0)) Then '*** mismatch error
    > > > frmEntry.lstSelected.AddItem frmEntry.lstSource.List(i)
    > > > End If
    > > > End If
    > > > Next i
    > > >
    > > > --
    > > > The enclosed questions or comments are entirely mine and don't represent

    > the
    > > > thoughts, views, or policy of my employer. Any errors or omissions are

    > my
    > > > own.

    > >
    > > --
    > >
    > > Dave Peterson


    --

    Dave Peterson

  5. #5
    KR
    Guest

    Re: avoiding duplicates in listbox (added from another listbox)

    Thanks Dave! I've adapted my code to add the items to an array (per your
    sample code) and the comparison works great- no duplicates!

    Many thanks,
    Keith

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > I built a userform with 3 Input Listboxes (listbox1, listbox2, listbox3)
    > and one "output" listbox (listbox4)
    >
    > And 4 commandbuttons (cancel, Process Listbox1, process listbox2, and

    process
    > listbox3):
    >
    > This was the code I used to test it:
    >
    > Option Explicit
    > Private Sub CommandButton1_Click()
    > Unload Me
    > End Sub
    > Private Sub CommandButton2_Click()
    > Call DoTheWork(Me.ListBox1)
    > End Sub
    > Private Sub CommandButton3_Click()
    > Call DoTheWork(Me.ListBox2)
    > End Sub
    > Private Sub CommandButton4_Click()
    > Call DoTheWork(Me.ListBox3)
    > End Sub
    > Private Sub UserForm_Initialize()
    > Dim iCtr As Long
    > Dim lCtr As Long
    >
    > 'some test data
    > For lCtr = 1 To 3
    > With Me.Controls("Listbox" & lCtr)
    > .MultiSelect = fmMultiSelectMulti
    > For iCtr = 1 To 5
    > .AddItem "asdf" & Format(iCtr, "00")
    > Next iCtr
    > End With
    > Next lCtr
    >
    > For iCtr = 1 To 3
    > Me.Controls("Commandbutton" & iCtr + 1).Caption _
    > = "Process ListBox " & iCtr
    > Next iCtr
    > End Sub
    > Sub DoTheWork(myListBox As Control)
    >
    > Dim iCtr As Long
    > Dim jCtr As Long
    > Dim res As Variant
    > Dim myArr As Variant
    >
    > If myListBox.ListCount > 0 Then
    > For iCtr = 0 To myListBox.ListCount - 1
    > If myListBox.Selected(iCtr) Then
    > If Me.ListBox4.ListCount = 0 Then
    > Me.ListBox4.AddItem myListBox.List(iCtr)
    > Else
    > ReDim myArr(0 To Me.ListBox4.ListCount - 1)
    > For jCtr = 0 To Me.ListBox4.ListCount - 1
    > myArr(jCtr) = Me.ListBox4.List(jCtr, 0)
    > Next jCtr
    > res = Application.Match(myListBox.List(iCtr), myArr,

    0)
    > If IsError(res) Then
    > 'add it
    > Me.ListBox4.AddItem myListBox.List(iCtr)
    > Else
    > Beep
    > End If
    > End If
    > End If
    > Next iCtr
    > End If
    > End Sub
    >
    >
    > KR wrote:
    > >
    > > There are three source lists that can populate the source listbox

    (changed
    > > by clicking on one of three cmdbuttons), as the user needs to add names

    from
    > > more than one of the lists. I've kept the lists separate because the
    > > grouping is important (for example, they may need to pick one name from
    > > /each/ list for a total of three unique names, but some names may be on

    2 or
    > > even all 3 lists).
    > >
    > > So they may look at source list 1, add one (or more) names, then click

    to
    > > see the second source list, add one (or more) names, etc. Since they

    can't
    > > use the same person more than once, it is more reliable to

    programatically
    > > prevent repeats rather than rely on the users to avoid duplicates.
    > >
    > > Should I just add the names to an array in memory instead of trying to

    match
    > > against the destination listbox? I can do that, I just thought there

    might
    > > be a more elegant way to compare directly to the array that is already

    in
    > > the listbox.
    > >
    > > FWIW, the example referenced in the URL is even more elegant than what

    I'm
    > > doing, as I'm not removing items from the source list (e.g. select with
    > > replacement) because otherwise I'd have to keep track of each list even

    when
    > > it wasn't showing (user switches lists, then switches back) and also

    which
    > > list(s) the duplicates names are on - I'd have to check all three source
    > > lists to remove a name that was selected, then know which list(s) to put

    the
    > > names back on if it was unselected.
    > >
    > > Thanks,
    > > Keith
    > >
    > > "Dave Peterson" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > How about deleting them from the first listbox when you add them to

    the
    > > second.
    > > > It might be easier to code and more intuitive for the user.
    > > >
    > > > This may give you an idea:
    > > > http://groups.google.com/groups?selm...BDD4%40msn.com
    > > >
    > > > KR wrote:
    > > > >
    > > > > I have a userform with a list of source names, a second list box in

    > > which to
    > > > > put selected names, and an add button to move the selected names to

    the
    > > > > second listbox.
    > > > >
    > > > > I need to avoid having the same name added more than once, and

    although
    > > the
    > > > > code below seems like a good idea to prevent duplicates, I'm getting

    a
    > > type
    > > > > mismatch. I think I'm mis-referencing the second element in the

    match
    > > > > statement, but I'm not sure how to reference the list inside the

    second
    > > > > listbox.
    > > > >
    > > > > Please help? I'm using XL2003
    > > > >
    > > > > Thanks,
    > > > > keith
    > > > >
    > > > > For i = 0 To frmEntry.lstSource.ListCount - 1
    > > > > If frmEntry.lstSource.Selected(i) = True Then
    > > > > If IsError(Application.Match(frmEntry.lstSource.List(i),
    > > > > frmEntry.lstSelected.List, 0)) Then '*** mismatch error
    > > > > frmEntry.lstSelected.AddItem

    frmEntry.lstSource.List(i)
    > > > > End If
    > > > > End If
    > > > > Next i
    > > > >
    > > > > --
    > > > > The enclosed questions or comments are entirely mine and don't

    represent
    > > the
    > > > > thoughts, views, or policy of my employer. Any errors or omissions

    are
    > > my
    > > > > own.
    > > >
    > > > --
    > > >
    > > > Dave Peterson

    >
    > --
    >
    > Dave Peterson




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1