How to sort a drop-down list of hidden sheets

[jqzhang]

Hi,
I have a Excel WorkBook with about 40 tabs hidden. Everytime when I try to unhide some of the sheets, I will have a pretty hard time looking for them because the list of the hidden files are not sorted.
So my question is does anybody have a ready-made macro that can sort the list everytime I open it, or I don't mind run a macro after the list drops down.
Thanks in advance!
jqzhang

 

[CautionMP]

Here is one solution, although not quite ready made.

Load the list rowsource values into an array, then call the following function to sort the array, then use the array to load the values in you list.

Private Sub ShellSortDescending(ArrayToSort() as string)
Dim intFinal As Integer
Dim intGap As Integer
Dim intFlag As Integer
Dim intCount As Integer
Dim strHolder As String

  intFinal = UBound(ArrayToSort)
  intGap = CInt(intFinal / 2)
  Do While intGap <> 0
    intFlag = 1
    For intCount = 1 To intFinal - intGap
      If ArrayToSort(intCount) < ArrayToSort(intCount + intGap) Then
        strHolder = ArrayToSort(intCount)
        ArrayToSort(intCount) = ArrayToSort(intCount + intGap)
        ArrayToSort(intCount + intGap) = strHolder
        intFlag = 0
      End If
    Next intCount
    If intFlag = 1 Then
      intGap = CInt(intGap / 2)
    End If
  Loop
End Sub

Hope this helps,
CMP

Instant programmer, just add coffee.

 

[SkipVought]

jqzhang,

Just reference the sheet. It does NOT need to be VISIBLE.

'Sheet2 is hidden
    With Sheet2
      .[A1].Sort _
       Key1:=.[A1], _
       Header:=xlYes
    End With

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]