[RESOLVED] Sort Listbox

[tome10]

I have this code that populates a Listbox with the sheets in the workbook.
I need a way to sort the items in the Listbox.

        Dim listbox1 As ListBox
            For Each ws In ThisWorkbook.Sheets

            If Not (ws.Name = "Data" Or ws.Name = "Index") _
                Then Sheets("Index").listbox1.AddItem ws.Name
            If ws.Index > 5 Then ws.Visible = False
                Next

[Siddharth Rout]

See if this helps?

http://www.vbforums.com/showthread.php?t=450689

[tome10]

Hey Sid! I couldn't get it to work.

I have this in the workbook module

        Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'            Application.ScreenUpdating = False
            If Sheets("Index").ToggleButton1.Value = False Then
                Dim ws As Worksheet
                Set ws = Sh
            If ws.Index > 5 Then
                ws.Move , Sheets("Index")
                Sheets("Index").ListBox1.Clear
                For Each ws In ThisWorkbook.Sheets
            If Not (ws.Name = "Data" Or ws.Name = "Index") _
                Then Sheets("Index").ListBox1.AddItem ws.Name
            If ws.Index > 5 Then ws.Visible = False
                Next
            End If
            End If
            SortListBox  'Hangs up here. (this is how you call another macro right?)
            
        End Sub

and I put this in a Module

 Sub SortListBox(oLb As MSForms.ListBox)
    Dim vaItems As Variant
    Dim i As Long, j As Long
    Dim vTemp As Variant
    
    ‘Put the items in a variant array
    vaItems = oLb.List
    
    For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
        For j = i + 1 To UBound(vaItems, 1)
            If vaItems(i, 0) > vaItems(j, 0) Then
                vTemp = vaItems(i, 0)
                vaItems(i, 0) = vaItems(j, 0)
                vaItems(j, 0) = vTemp
            End If
        Next j
    Next i
    
    ‘Clear the listbox
    oLb.Clear
    
    ‘Add the sorted array back to the listbox
    For i = LBound(vaItems, 1) To UBound(vaItems, 1)
        oLb.AddItem vaItems(i, 0)
    Next i
    
End Sub

[Hack]

Look at what you are doing

SortListBox  'Hangs up here. (this is how you call another macro right?)

Now, look at what the sub routine is expecting

Sub SortListBox(oLb As MSForms.ListBox)

Can you see why not only isn't it working, but you are getting an error (which you neglected to mention BTW)

[tome10]

Hack, the error is Compile Error: Argument not Optional
I tried putting the whole thing in SortListBox(oLb As MSForms.ListBox), but it comes back with an error immediately. Compile Error: Expected: List Seperator or ) with the "As" highlighted Blue.

[westconn1]

sortlistbox listbox1
the required argument is the listbox to sort
if you had multiple listboxes you can sort all by passing the listbox to sort

[Hack]

I knew what the error was before you posted it. You were calling the sub without passing it the parameter it required.

oLb As MSForms.ListBox is a parameter that must be passed. In this case, as westconn1 points out, it is the name of a listbox.

Anytime you use a sub or function that has listed parameters, or arguments, you HAVE to pass something to it in order to fulfill that requirement.

[anhn]

What you are doing is:
1. Clear the ListBox
2. Add everything you have into the ListBox
3. Copy the list of the ListBox to an array
4. Sort the array
5. Clear the ListBox again
6. Add the sorted array back to the ListBox

That is a crazy process! Why not sort while adding items into the ListBox?

With not too many items, you can use a simple linear search before adding an item in a right order place:

        With Sheets("Index").ListBox1
            .Clear
            For Each ws In ThisWorkbook.Sheets
                If ws.Name <> "Data" And ws.Name <> "Index" Then
                    For i = 0 To .ListCount - 1
                        If ws.Name < .List(i) Then Exit For
                    Next
                    .AddItem ws.Name, i
                End If
                If ws.Index > 5 Then ws.Visible = False
            Next
        End With

[tome10]

Hack, West, I'm can't seem to grasp what your saying.
I have tried:
Sub SortListBox(oLb As MSForms.ListBox1)
Call:
SortListBox Listbox1
Nothing works, what am I doing wrong?

[tome10]

Anhn, I tried your code, but it errors.

        Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'            Application.ScreenUpdating = False
            If Sheets("Index").ToggleButton1.Value = False Then
                Dim ws As Worksheet
                Set ws = Sh
            If ws.Index > 5 Then
                ws.Move , Sheets("Index")
                    With Sheets("Index").ListBox1.Clear
            For Each ws In ThisWorkbook.Sheets
                If ws.Name <> "Data" And ws.Name <> "Index" Then
                    For i = 0 To .ListCount - 1    'Errors here Runtime Error 424: Object required 
                                                            'Seems to error on sheet42 Varient Empty.
                        If ws.Name < .List(i) Then Exit For
                    Next
                    .AddItem ws.Name, i
                End If
                If ws.Index > 5 Then ws.Visible = False
            Next
        End With
            End If
            End If
        End Sub

[anhn]

My code is:

        With Sheets("Index").ListBox1
            .Clear

but you wrongly combined 2 lines into one:

With Sheets("Index").ListBox1.Clear

You should learn how to ident your code properly:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    'Application.ScreenUpdating = False
    If Sheets("Index").ToggleButton1.Value = False Then
        Dim ws As Worksheet
        If Sh.Index > 5 Then
            Sh.Move After:=Sheets("Index")
            With Sheets("Index").ListBox1
                .Clear
                For Each ws In ThisWorkbook.Sheets
                    If ws.Name <> "Data" And ws.Name <> "Index" Then
                        For i = 0 To .ListCount - 1
                            If ws.Name < .List(i) Then Exit For
                        Next
                        .AddItem ws.Name, i
                    End If
                    If ws.Index > 5 Then ws.Visible = False
                Next
            End With
        End If
    End If
End Sub

[tome10]

Anhn,

Ohhh! I remember doing that. I thought it was a mistake. my bad! ;-)

[Hack]

This should work just fine

SortListBox Listbox1

What happens when you run it?

[anhn]

If use SortListBox(), the call should be:

    SortListBox Sheets("Index").ListBox1

ListBox1 need to be qualified.

[tome10]

Anhn, I have both ways working now, but on your code I can't seem to figure out how to populate the listbox initially on Workbook Open. I have this:

        Private Sub Workbook_Open()

            Sheets("Data").Visible = False

            For Each ws In ThisWorkbook.Sheets
                If ws.Name <> "Data" And ws.Name <> "Index" Then
                    For i = 0 To .ListCount - 1      ' Compile Error: invalid or unqualified reference
                        If ws.Name < .List(i) Then Exit For
                    Next
                    .AddItem ws.Name, i
                End If
                If ws.Index > 5 Then ws.Visible = False
            Next
                Sheets("Index").Activate
            With Sheets("Index").ListBox1
                .Height = 92.4
                .Width = 184
                .Top = 79.2
                .Left = 249
            End With

        End Sub

[anhn]

Do you know how to use With...End With?

Private Sub Workbook_Open()
    Sheets("Data").Visible = False
    Sheets("Index").Activate
    With Sheets("Index").ListBox1
        .Height = 92.4
        .Width = 184
        .Top = 79.2
        .Left = 249
        For Each ws In ThisWorkbook.Sheets
            If ws.Name <> "Data" And ws.Name <> "Index" Then
                For i = 0 To .ListCount - 1
                    If ws.Name < .List(i) Then Exit For
                Next
                .AddItem ws.Name, i
            End If
            If ws.Index > 5 Then ws.Visible = False
        Next
    End With
End Sub

Without using
With Sheets("Index").ListBox1
the code becomes:

Private Sub Workbook_Open()
    Sheets("Data").Visible = False
    Sheets("Index").Activate
    Sheets("Index").ListBox1.Height = 92.4
    Sheets("Index").ListBox1.Width = 184
    Sheets("Index").ListBox1.Top = 79.2
    Sheets("Index").ListBox1.Left = 249
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Data" And ws.Name <> "Index" Then
            For i = 0 To Sheets("Index").ListBox1.ListCount - 1
                If ws.Name < Sheets("Index").ListBox1.List(i) Then Exit For
            Next
            Sheets("Index").ListBox1.AddItem ws.Name, i
        End If
        If ws.Index > 5 Then ws.Visible = False
    Next
End Sub

[tome10]

When I use both of the above codes, the Listbox is not formed correctly (too wide, too tall) on workbook open. When I go back in and manually run the code a 2nd time it is formed correctly. ideas?

[tome10]

For some reason the Listbox gets sized correctly when the sizing code is after the populating code. It is working now. Thanks for all your help.

Private Sub Workbook_Open()
    Sheets("Data").Visible = False
    Sheets("Index").Activate

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Data" And ws.Name <> "Index" Then
            For i = 0 To Sheets("Index").ListBox1.ListCount - 1
                If ws.Name < Sheets("Index").ListBox1.List(i) Then Exit For
            Next
            Sheets("Index").ListBox1.AddItem ws.Name, i
        End If
        If ws.Index > 5 Then ws.Visible = False
    Next
    Sheets("Index").ListBox1.Height = 95
    Sheets("Index").ListBox1.Width = 182
    Sheets("Index").ListBox1.Top = 79.2
    Sheets("Index").ListBox1.Left = 249
End Sub

[tome10]

Can you guys help me out on my other project? Please! ;-)

http://www.vbforums.com/showthread.php?t=616347