+ Reply to Thread
Results 1 to 14 of 14

Alphabetize dynamic ListBox Items

  1. #1
    Registered User
    Join Date
    03-30-2021
    Location
    Texas, United States
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Alphabetize dynamic ListBox Items

    I am new to the userform. I am also very new to VBA. I have created the attached spreadsheet from numerous examples on the web and have everything
    working as I need it. However I don't know how to keep the ListBox items alphabetized as it is updated when a New Entry is created in the Userform and the new entry goes on the last line.

    New Entry buton adds the information from the textboxes to a row in the underlying spreadsheet and adds the information as another line in the ListBox.

    Can you tell me what I need to add to the code in order to keep the ListBox in alphabetical order? Thank you for your time and assistance.
    All the data in the spreadsheet is ficticious.


    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,397

    Re: Alphabetize dynamic ListBox Items

    Now where have I seen that code structure before.
    In Module1 paste the code below over the LoadListBox() sub.
    it sorts on 'Last Name' first followed by 'First Name'
    torachan.

    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    709

    Re: Alphabetize dynamic ListBox Items

    Use indentation points and do not use a separate module. You can overcome everything directly within the form. I have omitted all unnecessary code and then come up with a simplified example.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-30-2021
    Location
    Texas, United States
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Alphabetize dynamic ListBox Items

    Thank you. When I pasted your code I got a Compile error Variable not defined on List = aryl.

    Sub LoadListBox()
    Dim sortcolumn1 As Range, sortcolumn2 As Range
    Set ws1 = Sheet1
    Set tbl1 = ws1.ListObjects("Table1")
    If tbl1.Range(2, 1) = "" Then Exit Sub
    Set sortcolumn1 = Range("Table1[Last Name]")
    Set sortcolumn2 = Range("Table1[First Name]")
    With tbl1.Sort
    .SortFields.Clear
    .SortFields.Add Key:=sortcolumn1, SortOn:=xlSortOnValues, Order:=xlAscending
    .SortFields.Add Key:=sortcolumn2, SortOn:=xlSortOnValues, Order:=xlAscending
    .Header = xlYes
    .Apply
    End With
    ary1 = tbl1.DataBodyRange
    With uf1.lbo1
    List = ary1
    ColumnCount = 12
    ColumnWidths = "50,50,70,70,80,60,60,70,30,40,70,50"
    End With
    End Sub

  5. #5
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    709

    Re: Alphabetize dynamic ListBox Items

    See #3

    Don't just pick a bad file from the internet. Without understanding anything about VBA.

  6. #6
    Registered User
    Join Date
    03-30-2021
    Location
    Texas, United States
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Alphabetize dynamic ListBox Items

    When I got your recommendation. When I removed LoadListBox from module and pasted your new code in uf1, The listbox did alphabetize but when I update, change or remove it updates in the list box on the userform but it no longer updates the spreadsheet. I'm trying to find this issue now.

  7. #7
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,397

    Re: Alphabetize dynamic ListBox Items

    @Vraag, very impressive, time to heed ones own advice, in giving advice perhaps it would be beneficial if you read and understood the entire code.
    There are many opinions as to correct code structure and reasons for such, the separate module contains several subs which are called several times throughout the program.
    Good structure demands that you keep repetitive code to a minimum.
    @cbel26, the code was checked and ran before i posted it, did you type it in, or did you copy/paste.
    you have stated 'aryl' it is ary1 (last character is 'one' not 'hell')
    torachan.

  8. #8
    Registered User
    Join Date
    03-30-2021
    Location
    Texas, United States
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Alphabetize dynamic ListBox Items

    I want to thank you for trying to help someone that doesn't know much about VBA I did copy and paste the code. the aryl was a typo on my part in the reply. I will continue to check my work. Thanks

  9. #9
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,397

    Re: Alphabetize dynamic ListBox Items

    attached file as altered and tested - only mods are in the module as previously stated.
    torachan.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,397

    Re: Alphabetize dynamic ListBox Items

    @cbel26, no matter the opinions of others, code presented by helpers should be understandable and aimed at the level of knowledge perceived that the OP has.
    We can all write 'one liners' that cause more bewilderment than assistance.
    However I have looked through posts in this thread and the reason it is not working is the absence of a (.) 'full stop' or I believe a 'point' in the USA.
    Compare my original code submission (the one between 'code tags' (as required by forum rules)) and your free text re-submission.
    The offending omission is directly preceding the word List (the smallest things can be the most important)
    torachan.

  11. #11
    Registered User
    Join Date
    03-30-2021
    Location
    Texas, United States
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Alphabetize dynamic ListBox Items

    Thank you so much. This now works perfectly for what I need. I appreciate the time you spent on this.

  12. #12
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    709

    Re: Alphabetize dynamic ListBox Items

    @torachan,
    very impressive, time to heed ones own advice, in giving advice perhaps it would be beneficial if you read and understood the entire code.
    There are many opinions as to correct code structure and reasons for such, the separate module contains several subs which are called several times throughout the program.
    Good structure demands that you keep repetitive code to a minimum.
    108 lines of code in 1 module or 212 lines in 2 modules. What will be clearer?
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-30-2021
    Location
    Texas, United States
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Alphabetize dynamic ListBox Items

    Could I dare ask you to help me with one other thing in the listbox in this thread? Is there a way to add the headers in the listbox? Thank you and I apologize for my lack of knowledge. After I complete this userform I will be taking VBA classes.

  14. #14
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,397

    Re: Alphabetize dynamic ListBox Items

    Below is a go-to reference link - contains almost all you want to know about about combo/list boxes.
    I have found it invaluable over the years and it gives a good analysis of the different methodology.
    Headers can be incorporated by using the 'rowsource' fill method although it can become unstable (especially if you have two or more workbooks open at the same time).
    Despite differing opinions using 'table' loaded 'arrays' is the most efficient as you keep the read/write to the sheet to a minimum.
    When people request headers, I usually use the approach as in the attached file, on the 'Re-Order page the listbox has external scroll bars that move the header caption label in sync with list.
    This app was built to the numerous requests of an OP on this site (the code has not been optomised - perhaps a challenge for someone).
    The previous app is now down to 95 lines (the downside - it comes with a 200 line guide to its structure)
    Bottom line - I would not by choice use 'rowsource'.
    torachan.

    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Add, Edit, Delete items in dynamic listbox
    By zvonacfirst in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-17-2017, 03:59 AM
  2. Replies: 1
    Last Post: 11-08-2014, 12:45 PM
  3. Alphabetize items in list box?
    By Nitefox in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-16-2014, 05:28 PM
  4. [SOLVED] Add Last Six Items to Listbox From dynamic range
    By s2jrchoi in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-26-2013, 02:35 PM
  5. Transfer random listbox items to new listbox and then loop through selected items
    By narrowgate88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-31-2013, 05:58 PM
  6. Excel Formula to Alphabetize Unique items only from a list
    By broshannon in forum Excel General
    Replies: 3
    Last Post: 09-06-2010, 08:07 AM
  7. [SOLVED] Alphabetize Combobox Items
    By XLVBA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-19-2010, 05:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1