visual basic for applications listbox sort

I'm trying to sort the contents of my listbox, but my version of VBA doesn't offer it as a property of the listbox (in the properties box).  What would the code be to do this?

Thanks
ylecoyoteAsked:
Who is Participating?
 
Richie_SimonettiConnect With a Mentor IT OperationsCommented:
Private Sub UserForm_Activate()
Dim l As Integer
l = 1
For i = 1 To Worksheets.Count
   If UCase$(Left$(Worksheets(i).Name, 9)) <> "TEMPLATE_" Then
       If UCase$(Worksheets(i).Name) = "LOG" Or _
       UCase$(Worksheets(i).Name) = "HELP" Or _
       Worksheets(i).Name = ActiveCell.Worksheet.Name Then
       Else
            'this would be hidden range
           Range("c" & l) = Worksheets(i).Name
           l = l + 1
       End If
   End If
Next i
Range("C1").End(xlDown).Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
lstHolder.RowSource = "C1:" & Range("C1").End(xlDown).Address
End Sub
0
 
Richie_SimonettiIT OperationsCommented:
From where do you are populating list box in first place?
if use a range, sort the range.
If you do manually, add items in right order.
Hope it helps
Cheers
0
 
Richie_SimonettiIT OperationsCommented:
If listbox is populated  with ramdom values, populate a hidden range first, sort that range and use it as RowSource property.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ylecoyoteAuthor Commented:
Thanks for your response.  I am still a little unclear.  I'm new to VB.  This is where and how I am populating the listbox.

The available worksheets change at any time, so they need to select from a list of available worksheets.  This macro is for an excel spreadsheet by the way.

+++++++++++++++++++++++++++++++++++++++++++++++++++++

Private Sub UserForm_Activate()

For i = 1 To Worksheets.Count
    If UCase(Left(Worksheets(i).Name, 9)) <> "TEMPLATE_" Then
        If UCase(Worksheets(i).Name) = "LOG" Or UCase(Worksheets(i).Name) = "HELP" Or Worksheets(i).Name = ActiveCell.Worksheet.Name Then
        Else
            lstHolder.AddItem (Worksheets(i).Name)
        End If
    End If
Next i

End Sub
0
 
Richie_SimonettiIT OperationsCommented:
Instead of populate
lstHolder.AddItem (Worksheets(i).Name)

populate a hidden range, sort it and use
lstHolder.RowSource to that range
0
 
DanRollinsCommented:
Hi ylecoyote,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept Richie_Simonetti's comment(s) as an answer.

ylecoyote, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.