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
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBS file using code from 2nd file (txt or vbs) 4 36
Best book to learn C++ 4 78
VBA filters 2 58
Interview questions for support of a legacy ASP.NET site 4 56
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now