Sortng Sheets in a List box Sheet navigator

bearcub

Well-known Member
Joined
May 18, 2005
Messages
701
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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

Rich (BB 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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
Another way:

Code:
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
 
Upvote 0
Thank you all for the code. They work perfectly now, all of them, thank you all for you kind help!

Michael
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top