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]

Try downloading & installing this
https://www.microsoft.com/en-us/download/confirmation.aspx?id=1639

 

[bearcub]

It seems I'm missing the files for this file that the link is pointing to. When I went to install it I got a message that I needed so other files (which I'm downloading now). So you think once I get all the files downloaded your code will work?

If this code does work on my computer, wouldn't I have problems on other computers that are missing the files I'm just downloading now?

Michael

 

[My Aswer Is This]

Here is a script I wrote that will load all the sheet names into a Activex Listbox like you want.
Why would a similar script not work in your case.
Just modify it to work for a Userform Listbox or Combobox.

Sub My_Sheet_Names()
'Modified  9/21/2018  6:36:49 PM  EDT
Dim i As Long
Dim lastrow As Long
    For i = 1 To Sheets.Count
        Sheets(1).Cells(i, 1).Value = Sheets(i).Name
    Next
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & lastrow).Sort key1:=Range("A1:A" & lastrow), _
   order1:=xlAscending, Header:=xlNo
ActiveSheet.ListBox1.List = Range("A1:A" & lastrow).Value
End Sub

 

[shg]

Another way:

Private Sub UserForm_Initialize()
   Dim wks As Worksheet
   
   ListBox1.RemoveAllItems
   
   For Each wks In Worksheets
      ListBox1.AddItem wks.Name
   Next wks
   
   SortTheBox ListBox1
End Sub

Function SortTheBox(vCtl As Variant) As Boolean
  ' sorts the ComboBox or ListBox passed as vCtl
  Dim i     As Long
  Dim j     As Long

  If Not IsObject(vCtl) Then Exit Function

  Select Case TypeName(vCtl)
    Case "ComboBox", "ListBox"
      With vCtl
        For i = 1 To .ListCount - 1
          For j = 0 To i - 1
            If .List(i) < .List(j) Then
              .AddItem .List(i), j
              .RemoveItem i + 1
              Exit For
            End If
          Next j
        Next i
      End With

      SortTheBox = True
  End Select
End Function

 

[bearcub]

Thank you all for the code. They work perfectly now, all of them, thank you all for you kind help!

Michael