+ Reply to Thread
Results 1 to 7 of 7

2d array sort and load to list box

Hybrid View

SPARKY347 2d array sort and load to... 12-08-2016, 12:17 AM
mikerickson Re: 2d array sort and load to... 12-08-2016, 01:12 AM
SPARKY347 Re: 2d array sort and load to... 12-08-2016, 01:33 AM
SPARKY347 Re: 2d array sort and load to... 12-08-2016, 02:04 AM
SPARKY347 Re: 2d array sort and load to... 12-08-2016, 02:11 AM
mikerickson Re: 2d array sort and load to... 12-08-2016, 10:00 AM
SPARKY347 Re: 2d array sort and load to... 12-08-2016, 11:12 AM
  1. #1
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    2d array sort and load to list box

    new at working with arrays hoping someone can help me heres what im trying to do. i would like a 2d array that loads 2 columns. column 1 = first names, column 2 = last names, i then would like to load these 2 columns into 2 seperate list boxes. with the list boxes i would like to then be able to sort the list boxes based on the value in the other list box.

    example from workbook= if i select allan in 1 list box i would like the array to sort and put BOTH his first and last name at the top of both list boxes i know this is possible just not how to code it.

    i do know how to add the values into the list boxes and work with the list box but am completely lost when it comes to the possibilities of arrays
    Attached Files Attached Files

  2. #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.

  3. #3
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: 2d array sort and load to list box

    that is excalty what i needed thank you

  4. #4
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: 2d array sort and load to list box

    one more thing sorry, see attached is it possible to add 1 more list box infront of the first name list box, call it member # list box
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: 2d array sort and load to list box

    Sorry I should have also mentioned I don't really need the buttons for the move to top and sort by last and first

  6. #6
    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

    The third listbox is a minor tweak, but I don't understand why no buttons.

    When do you want the list sorted?
    When do you want the selected name to move to the top?

    Also, if these three list boxes are going to be completely synchronized, it would be much simpler to just have a single listbox showing all three columns.

  7. #7
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: 2d array sort and load to list box

    How can I do the single lost box? Sorry regarding the sorting I don't really need it sorted I guess. Using the combo box works great. As I select a value it grabs the other ones at the same time which is exactly how I want t it

+ 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. 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. 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