visual basic for applications listbox sort

Posted on 2002-05-02
Last Modified: 2013-11-25
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?

Question by:ylecoyote
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 16

Expert Comment

ID: 6986637
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
LVL 16

Expert Comment

ID: 6986664
If listbox is populated  with ramdom values, populate a hidden range first, sort that range and use it as RowSource property.

Author Comment

ID: 6986711
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
            lstHolder.AddItem (Worksheets(i).Name)
        End If
    End If
Next i

End Sub
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 16

Expert Comment

ID: 6987677
Instead of populate
lstHolder.AddItem (Worksheets(i).Name)

populate a hidden range, sort it and use
lstHolder.RowSource to that range
LVL 16

Accepted Solution

Richie_Simonetti earned 50 total points
ID: 6987691
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
            'this would be hidden range
           Range("c" & l) = Worksheets(i).Name
           l = l + 1
       End If
   End If
Next i
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
LVL 49

Expert Comment

ID: 7698798
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

Expert Comment

ID: 7755588
per recommendation

Community Support Moderator @Experts Exchange

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question