?
Solved

visual basic for applications listbox sort

Posted on 2002-05-02
7
Medium Priority
?
166 Views
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?

Thanks
0
Comment
Question by:ylecoyote
[X]
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
7 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
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
Cheers
0
 
LVL 16

Expert Comment

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

Author Comment

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

End Sub
0
Technology Partners: 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

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

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

Accepted Solution

by:
Richie_Simonetti earned 200 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
       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
 
LVL 49

Expert Comment

by:DanRollins
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
0
 

Expert Comment

by:SpideyMod
ID: 7755588
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

777 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