+ Reply to Thread
Results 1 to 12 of 12

How to keep items in ListBox in alphabetical order when removing and adding items to box

Hybrid View

welchs101 How to keep items in ListBox... 01-30-2015, 12:08 PM
shg Re: How to keep items in... 01-30-2015, 12:39 PM
welchs101 Re: How to keep items in... 01-30-2015, 01:49 PM
shg Re: How to keep items in... 01-30-2015, 02:04 PM
welchs101 Re: How to keep items in... 01-30-2015, 02:29 PM
welchs101 Re: How to keep items in... 01-30-2015, 05:44 PM
welchs101 Re: How to keep items in... 01-30-2015, 06:01 PM
shg Re: How to keep items in... 01-30-2015, 06:25 PM
welchs101 Re: How to keep items in... 01-30-2015, 06:35 PM
shg Re: How to keep items in... 01-30-2015, 08:00 PM
mikerickson Re: How to keep items in... 01-30-2015, 11:20 PM
welchs101 Re: How to keep items in... 01-31-2015, 07:13 AM
  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    How to keep items in ListBox in alphabetical order when removing and adding items to box

    Hi,

    I have a two listboxes on a user form.
    One listbox (A) is an alphabetical list of names. Another listbox (B) contains those name the user selected from listboxA.

    Once the user selects an item in listboxA that item is removed from listboxA and moved to listboxB. However, if user selects name in listboxB and wants to put this name back into listboxA i need it put back into alphabetical order..........any ideas how to do this?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    Sub demo()
      Sheet1.ComboBox1.List = Range("A2:A11").Value
      SortTheBox Sheet1.ComboBox1
     
      Sheet1.ListBox1.List = Range("B2:B11").Value
      SortTheBox Sheet1.ListBox1
    End Sub
    
    Function SortTheBox(vCtl As Variant) As Boolean
      Dim i     As Long
      Dim j     As Long
    
      If Not IsObject(vCtl) Then Exit Function
    
      Select Case TypeName(vCtl)
        Case "ComboBox", "ListBox"
          With vCtl
            For i = 1 To .ListCount - 1
              For j = 0 To i - 1
                If .List(i) < .List(j) Then
                  .AddItem .List(i), j
                  .RemoveItem i + 1
                  Exit For
                End If
              Next j
            Next i
          End With
    
          SortTheBox = True
      End Select
    End Function
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    hi, i found some code online before i saw your post......it works "some of the time" i am enclosing the file so you can see what i am doing.

    i cant seem to repeat the error .........any idea why this is not working

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    Did you try the code I posted?

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    no i did not .........i was trying to figure out the code i was working on as it seemed to be almost right. i will keep trying for alittle longer my code to figure out error then i will try yours.........thanks.

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    quick question...........about my code........

    for some reason when i compare a string to another string.......example: string1 = "de something" and string2 = "Zsomething"
    its saying that the "de something" is greater than the "Zsomething" which does not make sense.....

    any idea what is going on?

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    fyi:

    string1 = "de krom"
    string2 = "Zack,Jobin L"

    my code when it runs says string1 is > than string2

    it shouldnt be should it?

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    String comparison in VBA is case-sensitive by default.

  9. #9
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    so "d" is greater than "Z"......huh, did not know this

    so should i convert everything to either upper case or lower case or should i use the strcomp function

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    Roger. You could also use Option Compare Text, but I wouldn't recommend it.

  11. #11
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    If the ListBox is already sorted, this routine will add text and maintain the sort.

    Sub AddToSortedListbox(textToAdd As String, ListBox As MSForms.ListBox, Optional Descending As Boolean = False)
        Dim i As Long
        With ListBox
            For i = 0 To .ListCount - 1
                If (textToAdd < .List(i)) Xor Descending Then
                    .AddItem textToAdd, i
                    Exit Sub
                End If
            Next i
            .AddItem textToAdd
        End With
    End Sub
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  12. #12
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: How to keep items in ListBox in alphabetical order when removing and adding items to b

    thanks you all.

    here is what i ended up with. again i had found some code online and started with that before i saw anyones post here. I just replaced one line of code with a StrComp and it seems to work. thanks again. Took me a while to figure out why it was not working.......but learned something again. thanks.

    For i = Items_Selected.ListCount - 1 To 0 Step -1
    
        If Items_Selected.Selected(i) = True Then
        
                If Items_to_Select.ListCount = 0 Then
                    'this is the first item
                    Items_to_Select.AddItem Items_Selected.List(i)
                ElseIf Items_Selected.List(i) > Items_to_Select.List(Items_to_Select.ListCount - 1) Then
                    'new item goes at the end of the list
                    Items_to_Select.AddItem Items_Selected.List(i)
                Else
                    'goes somewhere else
                    For Alpha = 0 To Items_to_Select.ListCount - 1
                        '*replaced this*'If Items_Selected.List(i) < Items_to_Select.List(Alpha) Then
                        If StrComp(Items_Selected.List(i), Items_to_Select.List(Alpha), vbTextCompare) = -1 Then
                            Items_to_Select.AddItem Items_Selected.List(i), Alpha
                            Exit For
                        End If
                    Next Alpha
                End If
                      
                Items_Selected.RemoveItem i
                                       
        End If
    
    Next i

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How do I place items in alphabetical order in a column and keep da
    By Angela33 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  2. How do I place items in alphabetical order in a column and keep da
    By Angela33 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  3. How do I place items in alphabetical order in a column and keep da
    By RagDyer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  4. How do I place items in alphabetical order in a column and keep da
    By Angela33 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  5. [SOLVED] How do I place items in alphabetical order in a column and keep da
    By Angela33 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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