Access VBA Useful ways to sort/search a ListBox

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I have a ListBox that has thousands of items in it. Each item contains several numbers and caharcters.
I want to put a few buttons in to do useful searching and sorting. Can anyone suggest a few ways to do this? As you can see in the image the list contains several preceding numbers

1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Personally, with a list like that, I'd add a textbox which would filter the combo box down to a manageable list.

You mention buttons, what did you have in mind?  If you have categories, ... then you could include combo boxes that when the selection changes it filters the listbox.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks. I wanted a "Sort Asc" and "Sort Desc" buttons
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
also the listbox copied data from another control. How would I filter the value list
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Use a query to fill the listbox, and add an ORDER BY clause. By default, queries are sorted ASCending, so:

SELECT * FROM SomeTable ORDER BY SomeField DESC

To remove that sort:

SELECT * FROM SomeTable ORDER BY SomeField
Mark EdwardsChief Technology Officer

Commented:
Value List as rowsources for controls is what you use when you DON'T want/need to perform actions like sort and filter on the list.  That is best left to SQL/Table/Query rowsources.
Value List are usually strings delimited  by a semi-colon between entries (see what your list is using).  The number of columns determine how many items in the Value List string are on a row (don't forget to include hidden columns).
If there is only one column on the listbox and each item in the value list is a row in the listbox, then you can easily run the value string thru the Split() function to bust it into an array.
You can then put the array into a table.
You can then create an SQL statement with the table and apply any kind of sorting/filtering you want to do.
Google "Access VBA Split function" and "Using Access VBA Arrays" as training exercises.
If it gets over your head, I'll send you a sample.
Another option is to cycle thru the listbox entries and append them to a table.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi. Thanks. Yes please could you send a sample. This sounds like what I need
Chief Technology Officer
Commented:
ok.  Here it is.  Chose the technique you want to use.  (Open Form1)
SampleProcesses.accdb
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial