Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
Sign in to follow this  
ZiyaSepp

Populate and Sorting a List box

Question

Hello Caleeco, 

 

Was hoping you would have some time to help solve a dashboard problem for me! I have a Spreadsheet where i need to populate a Listbox with dynamic data from Column B. However, I need the List box to be sorted alphabetically.

 

Is this something that can be done in the userform? or do I have to AtoZ sort to data before populating the list box?

Your help would be amazing

Thanks

Z

Share this post


Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0

Give this a go, place this code in the Userform Codesheet

VBEditor > Double Click UserForm1 in the Project Window > Double Click anywhere in the Userform

 

Private Sub UserForm_Initialize()
'//Source: www.ExcelWTF.com
'//Purpose: Initialise and Sort Listbox from Sheet
    Dim i As Long, j As Long
    Dim SortBox As Variant
    
    With Sheets("Data")
        ListBox1.List = .Range("B1", .Range("B1").End(xlDown)).Value
    End With

    With ListBox1
        For i = 0 To .ListCount - 2
            For j = i + 1 To .ListCount - 1
                If .List(i) > .List(j) Then
                    SortBox = .List(j)
                    .List(j) = .List(i)
                    .List(i) = SortBox
                End If
            Next j
        Next i
    End With
End Sub

Let me know how you get on :)

Caleeco

  • Thanks 1

Share this post


Link to post
Share on other sites
  • 0

Hey Ziya, 

 

Good to see you back on the forum! :D Yes, that shouldn't be too difficult. Some questions for you:

  1. Is this an alphabetic or numeric sort?
  2. What is the sheet name with the data?
  3. What are the assigned names for your Userform and Listbox (Check the Properties window in the VBEditor)

Let me know

Caleeco

  • Like 1

Share this post


Link to post
Share on other sites
  • 0
4 minutes ago, Caleeco said:

Hey Ziya, 

 

Good to see you back on the forum! :D Yes, that shouldn't be too difficult. Some questions for you:

  1. Is this an alphabetic or numeric sort?
  2. What is the sheet name with the data?
  3. What are the assigned names for your Userform and Listbox (Check the Properties window in the VBEditor)

Let me know

Caleeco

 

Hey Hey

 

Thanks! No problemo, see answers below

  1. Alphabetic
  2. "Data"
  3. Just the defaults "Userform1" & "Listbox1"

Thank you :)

Z

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

LOL! I was googling for solutions... most of them were like 100+ lines long xD

 

Thanks for the code, it works perfectly! And lightning quick :D

 

Z

ps the new layout looks great! :x

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×