Solved

visual basic for applications listbox sort

Posted on 2002-05-02
7
159 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
7 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
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 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS Access - Capture pressed key onclick 4 21
Run code from text file in vb 1 32
VBA filters 2 37
Using MS Code on my Mac 6 44
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

772 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

10 Experts available now in Live!

Get 1:1 Help Now