Sortng Sheets in a List box Sheet navigator

[bearcub]

I have a list box that I have on a userform that lists all the workbook sheets and then goes to the sheet that is selected. I'm trying to alphabetically sort the sheets in the sheet box. I found a code snippet on the internet but I'm not sure where to call or put the macro. Here are the code snippets that I have. How do I incorporate the sort code into the main code

Private Sub CommandButton1_Click()
    Dim i As Integer, sht As String
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            sht = ListBox1.List(i)
        End If
        Call RunTheSortMacro
    Next i

    Sheets(sht).Activate
    End
 End Sub

This is the Sort Code that I found this morning that I don't know how to incorporate into the listbox code

Sub RunTheSortMacro()
Dim i As Long
Dim myArray As Variant

'Set the array
myArray = Array("p", "A", "G", 3, "l", "6", 10, "K", 7)

'myArray variable set to the result of SortArrayAtoZ function
myArray = SortArrayAtoZ(myArray)

'Output the Array through a message box
For i = LBound(myArray) To UBound(myArray)
    MsgBox myArray(i)
Next i
End Sub

Function SortArrayAtoZ(myArray As Variant)
Dim i As Long
Dim j As Long
Dim Temp

'Sort the Array A-Z
For i = LBound(myArray) To UBound(myArray) - 1
    For j = i + 1 To UBound(myArray)
        If UCase(myArray(i)) > UCase(myArray(j)) Then
            Temp = myArray(j)
            myArray(j) = myArray(i)
            myArray(i) = Temp
        End If
    Next j
Next i

SortArrayAtoZ = myArray
End Function

I tried to call both the function and the sub into the list box code but nothing seems to work. I'm scratching my head trying to figure out how to incorporate the array code into the main code.

Thank you for your help,

Michael

 

[Fluff]

You need to sort the data before putting it in the listbox, like

Private Sub UserForm_Initialize()
   Dim Lst As Object
   Dim Ws As Worksheet
   Set Lst = CreateObject("system.collections.arraylist")
   For Each Ws In Worksheets
      Lst.Add Ws.Name
   Next Ws
   Lst.Sort
   Me.ListBox1.List = Lst.toarray
End Sub

 

[bearcub]

I see, so this is why it isn't working, I would need to put something in the initialize event?

I have the following code in the Initialize event:

Public Sub UserForm_Initialize()
Dim n As Long
Dim Lst As Object
Dim Ws As Worksheet


For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            sht = ListBox1.List(i)
        End If
    Next i
For n = 1 To ActiveWorkbook.Sheets.Count
   If Sheets(n).Visible = True Then ListBox1.AddItem ActiveWorkbook.Sheets(n).Name
Next n
End Sub

I tried to insert your code snippet into this routine but I got an error message in the Sub Open_Userform) routine:

Sub Open_Useform()
UserForm1.Show

End Sub

The UserForm.Show line was highlighted in yellow. When I ran the routine without the sort code you provided I got a runtime error.

 

[Fluff]

Replace your entire initialise code with this

Private Sub UserForm_Initialize()
   Dim Lst As Object
   Dim Ws As Worksheet
   Set Lst = CreateObject("system.collections.arraylist")
   For Each Ws In ActiveWorkbook.Worksheets
      If Ws.Visible = True Then Lst.Add Ws.Name
   Next Ws
   Lst.Sort
   Me.ListBox1.List = Lst.toarray
End Sub

 

[bearcub]

thank you Fluff,

when I apply this the initialize event I got an automation error,which is weird. I can see where this would work but I don't know why I'm getting the automation error

 

[Fluff]

In the VBE > Tools > references > scroll down & see if you have mscorlib.dll (they should be in alphabetical order)

 

[bearcub]

I did see that library dll and checked the box. But, I still get an automation error. I removed the existing code in the initialize event and replace it with yours. I must to doing something wrong.

 

[Fluff]

You don't need to check the box, as long as it's there.
In the VBE > Tools > Options > General > select Break in class module > OK
The put the cursor anywhere in the Initialise event & press F8, continue doing that until you get an error.
What line is highlighted & what is the exact error message?

 

[bearcub]

I found the problem.

The error code is -2146232576 (8031700): Automation Error

The error is created when it is attempting to Set Lst = CreateObject("system.collections.arraylist")

Could it be that I don't have that library selected or in my VBA module?

Michael

 

[bearcub]

Do you think that error is caused because it can't find the library for CreateObject("system.collections.arraylist")