Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Unanswered: help vba listbox in excel

    hey guys,

    i've been having trouble passing the name of listbox to a function. you can see the code below:

    Code:
    Function Sort(ByVal listname As ListBox)
        Dim a As Long
        Dim j As Long
        Dim sTemp As String
        Dim sTemp2 As String
        Dim LbList As Variant
        
        'lb = listname.Name
        x = listname.Width
        MsgBox x
        
        'Store the list in an array for sorting
        LbList = listname.List
        MsgBox LbList(2)
        'Bubble sort the array on the first value
        For a = LBound(LbList, 1) To UBound(LbList, 1) - 1
            For j = a + 1 To UBound(LbList, 1)
                If LbList(a, 0) > LbList(j, 0) Then
                    'Swap the first value
                    sTemp = LbList(a, 0)
                    LbList(a, 0) = LbList(j, 0)
                    LbList(j, 0) = sTemp
                    
                    'Swap the second value
                    sTemp2 = LbList(a, 0)
                    LbList(a, 1) = LbList(j, 1)
                    LbList(j, 1) = sTemp2
                End If
            Next j
        Next a
        
        'Remove the contents of the listbox
        listname.Clear
        
        'Repopulate with the sorted list
        listname.List = LbList
    End Function
    
    Private Sub CommandButton3_Click()
    '===list1 is the name of listbox the i want to pass   
         sorted (List1)
    End Sub
    Last edited by loquin; 03-09-07 at 15:36. Reason: add [code] [/code] tags for readability, moved to Excel forum

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I don't think you can pass a form object byval. Pass it ByRef.

    The other obvious question is, why not just set the listbox.Sorted property to true?
    Last edited by loquin; 03-09-07 at 15:23.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Whoops. Didn't notice that this was in VBA, not VB. I don't know if Excel's listbox has a "sorted" property.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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