+ Reply to Thread
Results 1 to 8 of 8

Sort selected userform listbox items alphabetically

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Sort selected userform listbox items alphabetically

    I found a code that sorts a userform listbox alphabetically.
    Was wondering if there is a way to first sort all the selections alphabetically witht he first selections at be displayed at the top of the list box and then all the unselected items (already in alphabetical order) to be displed below all the selections?

    I know i'm way off but i tried the best i could, see below:

    Dim i As Long
    Dim j As Long
    Dim temp As Variant
       
    With Me.BrandListBox
        For j = 0 To BrandListBox.ListCount - 2
            For i = 0 To BrandListBox.ListCount - 2
            If .Selected(i) = True Then
                If .List(i) > .List(i + 1) Then
                    temp = .List(i)
                    .List(i) = .List(i + 1)
                    .List(i + 1) = temp
                End If
            End If
            Next i
        Next j
    End With
    I really would appreciate any help on this.

  2. #2
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Re: Sort selected userform listbox items alphabetically

    i was thinking that maybe the code can achieve the same goal by removing the selected item from the list box and then adding it to the top of the list box like this:
    remove:
    BrandListBox.RemoveItem BItem
    add:
    BrandListBox.AddItem BItem, 0
    as the "0" adds the item to the top of the list.
    One of the problems i am having is that "Bitem" is not a string but rather a number. And the other problem is...i have no clue what i am doing
    Once again i am seeking some guidance.

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Sort selected userform listbox items alphabetically

    You should extract the selected items to an array, sort what's left, sort the extracted array and add the extracted items back at the top using AddItem.

    As for sorting numerically you'll need to convert the values to numbers when comparing.

    That's the theory anyway, can't post any code right now, not at a computer.
    If posting code please use code tags, see here.

  4. #4
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: Sort selected userform listbox items alphabetically

    Maybe something like this:
    1. assign selected item value to a variable (k)
    2. change selected item value to 0 (so it will be on top when sorted)
    3. sorting
    4. change listindex(0) value to k
    Dim i As Long
    Dim j As Long, k
    Dim temp As Variant
       
    With Me.ListBox1
       If .ListIndex <> -1 Then
       k = .Value
       .List(.ListIndex) = 0
       Else
       Exit Sub
       End If
        
        For j = 0 To ListBox1.ListCount - 2
            For i = 0 To ListBox1.ListCount - 2
                If .List(i) > .List(i + 1) Then
                    temp = .List(i)
                    .List(i) = .List(i + 1)
                    .List(i + 1) = temp
                End If
    
            Next i
        Next j
        .List(0) = k
    End With

  5. #5
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Re: Sort selected userform listbox items alphabetically

    Quote Originally Posted by Akuini View Post
    Maybe something like this:
    Hi Akuini and Norie,
    Thank you Akuini for your code and thanks Norie for the idea which i started working on but looks like a it's a long road for me.
    In Akuini's code i changed a couple things like removing the zero which won't get removed by the code. I also had the code re-select the item that was sorted to the top since it got un-selected.
    I'm having trouble though when applying your code to a multi-select list-box, code works fine when the list-box is limited to one selection.
    Also, i inserted this bit to re-sort the items when a different selection is made in the combo-box:
    With BrandListBox
        For i = 0 To .ListCount - 2
            For j = i + 1 To .ListCount - 1
                If .List(i) > .List(j) Then
                    Temp = .List(j)
                    .List(j) = .List(i)
                    .List(i) = Temp
                End If
            Next j
        Next i
    End With
    and this bit to deselect the previously selected items:
    For BItem = 0 To BrandListBox.ListCount - 1
        BrandListBox.Selected(BItem) = False
    Next
    , however, (and i think this may be what Norie was touching on) the items are mixed up internally. Meaning, even though the items are displayed properly they are not selected properly by the code anymore.
    See attached workbook (the code will debug since the listbox is set to multi-select, if you change it to single select the code should run but it won't run properly)
    Attached Files Attached Files
    Last edited by kosherboy; 04-19-2016 at 01:41 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: Sort selected userform listbox items alphabetically

    I made an example regarding how to sort listbox with multi select options.
    I use 'System.Collections.ArrayList' object to populate the list because this object has 'built in' sort function.
    This is how it work:
    1. user pick an item in combobox. It will sort the listbox and clear selection.
    2. user pick some item in listbox
    3. hit the command button
    4. the selected item will be on top & the list below it is sorted.

    Private Sub ComboBox1_Change()
    Dim dar As Object, r As Range
    
    Set dar = CreateObject("System.Collections.ArrayList")
    For Each r In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    dar.Add r.Value
    Next
    dar.Sort
    ListBox1.List = dar.toarray()
    End Sub
    
    
    
    Private Sub CommandButton1_Click()
    Dim dar As Object, i As Long
    Set dar = CreateObject("System.Collections.ArrayList")
    With ListBox1
        'get selected item to dar
        For i = 0 To .ListCount - 1
            If .Selected(i) = True Then
            dar.Add .List(i)
            .List(i) = 0 'selected item become 0
            End If
        Next i
        
        'get listbox list to dar1 and the sorted
        Set dar1 = CreateObject("System.Collections.ArrayList")
        For i = 0 To .ListCount - 1
            dar1.Add .List(i)
        Next i
        dar1.Sort
        ListBox1.List = dar1.toarray()
        
        'get dar value back to selected item
        dar.Sort
        For i = 0 To dar.Count - 1
        .List(i) = dar.toarray()(i)
        .Selected(i) = True
        Next
    
    End With
    
    End Sub
    
    
    
    Private Sub UserForm_Initialize()
    
    Dim dar As Object, r As Range
    ListBox1.MultiSelect = fmMultiSelectMulti
    Set dar = CreateObject("System.Collections.ArrayList")
    For Each r In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    dar.Add r.Value
    Next
    dar.Sort
    ListBox1.List = dar.toarray()
    
    Set dar = Nothing
    
    Set dar = CreateObject("System.Collections.ArrayList")
    For Each r In Range("B2", Cells(Rows.Count, "B").End(xlUp))
    dar.Add r.Value
    Next
    dar.Sort
    ComboBox1.List = dar.toarray()
    
    End Sub
    This the workbook:
    https://www.dropbox.com/s/ttm0pqmtcd...list.xlsm?dl=0

  7. #7
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Re: Sort selected userform listbox items alphabetically

    Amazing show! You sure know how to code!
    I'm speechless.
    Thank you for your time on this and keep in mind that i appreciate your knowledge very much

  8. #8
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: Sort selected userform listbox items alphabetically

    Quote Originally Posted by kosherboy View Post
    Amazing show! You sure know how to code!
    I'm speechless.
    Thank you for your time on this and keep in mind that i appreciate your knowledge very much
    You're welcome & thanks for your kind word.

+ 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] Userform - ListBox items added based on Combobox value selected
    By jayherring86 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-12-2014, 09:12 AM
  2. [SOLVED] Add Selected Items From One ListBox to Another ListBox on UserForm
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2014, 06:53 PM
  3. Sort combobox items alphabetically
    By aprilapple04 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-16-2013, 03:12 AM
  4. Transfer random listbox items to new listbox and then loop through selected items
    By narrowgate88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-31-2013, 05:58 PM
  5. How to sort a listbox alphabetically?...
    By 10121730 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2012, 03:03 AM
  6. Transfer Selected UserForm ListBox Items into a new Row
    By oumomof3 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-25-2011, 02:02 PM
  7. Sort combobox items alphabetically
    By VishalGupta in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-06-2011, 01:13 AM

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