Results 1 to 7 of 7

2d array sort and load to list box

Threaded View

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

    Re: 2d array sort and load to list box

    I think the attached will do what you want.

    Each list box holds both names, but with a different column hidden.

    The SortListBox sub will sort a list box by the column indicated (defaults to column 0)

    The ListBoxes are connected via Change event so that the selecting a name in one list box will select the matching name in the other list box.

    Pressing the added button will move the selected name to the top of both list boxes.

    ' in userform code module
    
    Dim DisableMyEvents As Boolean
    
    Private Sub box_first_Click()
        If DisableMyEvents Then Exit Sub
        DisableMyEvents = True
        box_last.ListIndex = box_first.ListIndex
        DisableMyEvents = False
    End Sub
    
    Private Sub box_last_Click()
        If DisableMyEvents Then Exit Sub
        DisableMyEvents = True
        box_first.ListIndex = box_last.ListIndex
        DisableMyEvents = False
    End Sub
    
    Private Sub CommandButton1_Click()
        Call MoveToTop
    End Sub
    
    Private Sub CommandButton2_Click()
        SortListbox box_first, 0
        SortListbox box_last, 0
    End Sub
    
    Private Sub CommandButton3_Click()
        SortListbox box_first, 1
        SortListbox box_last, 1
    End Sub
    
    Private Sub UserForm_Initialize()
        With box_first
            .ColumnCount = 2
            .ColumnWidths = ";0"
            .List = Range("B6:C13").Value
        End With
        With box_last
            .ColumnCount = 2
            .ColumnWidths = "0;"
            .List = Range("B6:C13").Value
        End With
    End Sub
    
    Sub MoveToTop()
        Dim FirstName As String
        Dim SecondName As String
        With box_first
            If .ListIndex <> -1 Then
                FirstName = .List(.ListIndex, 0)
                SecondName = .List(.ListIndex, 1)
                box_last.RemoveItem .ListIndex
                .RemoveItem .ListIndex
                .AddItem FirstName, 0
                .List(0, 1) = SecondName
                box_last.AddItem FirstName, 0
                box_last.List(0, 1) = SecondName
                .ListIndex = 0
            End If
        End With
    End Sub
    
    Sub SortListbox(ListBox As MSForms.ListBox, Optional SortColumn As Long = 0)
        Dim ItemCount As Long
        Dim currentItem As Long
        Dim i As Long, j As Long
        DisableMyEvents = True
        With ListBox
            ItemCount = .ListCount
            For currentItem = 0 To ItemCount - 1
                For i = ItemCount To .ListCount - 1
                    If LCase(.List(currentItem, SortColumn)) < LCase(.List(i, SortColumn)) Then Exit For
                Next i
                
                .AddItem .List(currentItem, 0), i
                For j = 1 To .ColumnCount - 1
                    .List(i, j) = .List(currentItem, j)
                Next j
                If currentItem = .ListIndex Then .ListIndex = i
            Next currentItem
            For i = 0 To ItemCount - 1
                .RemoveItem 0
            Next i
        End With
        DisableMyEvents = False
    End Sub
    Attached Files Attached Files
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Load Values To Array Using .CurrentRegion With Array Base 0
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-01-2016, 07:53 AM
  2. Load an array from ADO recorset
    By cgkmal in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-18-2014, 04:07 PM
  3. [SOLVED] Load PageBreaks for a Range in an Array
    By cmore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2014, 11:17 PM
  4. Load data to two-dimensional array
    By Mr_Knows_Nothing in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-04-2013, 06:58 PM
  5. Load Values into Array to Compare
    By prescient in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2009, 05:11 PM
  6. Load Contents of array into Range
    By additude in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-26-2008, 07:01 PM
  7. [SOLVED] Load an array with Sheet names
    By S G Booth in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-02-2005, 05:06 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