New offering: Class to handle multi key presses in sorted list

  • Thread starter Thread starter Robin Hammond
  • Start date Start date
R

Robin Hammond

This one stemmed from frustration with trying to select items in a list of
several thousand alphabetically sorted items. Unlike the Windows list
handlers, Excel only reacts to a single key press. Pressing "A" then "B" in
quick succession will move you to the first entry starting in "B", not to
those starting "AB". So, rather than write custom code for each list with
lots of entries, I created a class module that can be initialised for any
list in a form. Multiple class objects can also be created and assigned to
multiple lists. Usage is a doddle. All it needs is three lines of code.

The class is available for anyone who wants to try it out on my site at the
link below.
http://www.enhanceddatasystems.com/ED/Pages/ExcelListHandler.htm

Please let me know if you find ways to improve on it.

Robin Hammond
www.enhanceddatasystems.com
 
Robin,

Nice... BUT
I've made following changes to your "normal" listbox
and to me there's hardly any difference.


Properties:
MatchEntry -> MatchEntryComplete

Private Sub lstNorm_change()
With lstNorm
If Left(.List(.TopIndex), 2) <> Left(.List(.ListIndex), 2) Then
lstNorm.TopIndex = lstNorm.ListIndex
End If
End With
End Sub



Ofcourse the above is cheating a little.. since your test items
are 3 chars only, but i doubt if testing with longer strings would make a
big difference.


sorry ;-)
 
I think I'll just go and bury my head in the sand as far as basic lists are
concerned. There's some upside from the inputtime control I guess. Oh well,
just trying to be helpful.

Robin
 
Robin,

I did some further testing on your handler.
with a different set of items... (40320 permuts of "ABCDEFGH")


Your handler will not test on the existence of an item.
Rather it will generated an error and your handler will show
the generic messagebox "an error occured in the listhandler"

rather confusing..


Further in your handling code I found that your class appears to be
handling MultiSelect boxes...

And although multiselect is rather difficult to combine with typing,
I thought I'd give it a try... and set the "handled" box to
multiselectextended... which throws an error on each keypress.
 
Robin,

I'm sure I must be missing something but doesn't changing MatchEntry
from fmMatchEntryFirstLetter to FmMatchEntryComplete achieve
same, namely type "AB" and find the first item starting "AB", instead of
"B".

Regards,
Peter
 
Yes, in most cases. I think the reason I got off on this tangent was that I
was the built in method for a multi-select list box and matchentry doesn't
seem to work. Since KeepItCool's comments I've gone back and tested again
and the built in version works fine on a single select box. I think this one
is going to go down in the obscure file pretty soon, but since I use a lot
of multi-select lists it'll be useful to me anyway.

Robin Hammond
www.enhanceddatasystems.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to sort list automatically in alphabetical order 6
Algorithm to combine identical items in a list 1
Code to automatically sort a list, each time that a new entry isadded to the bottom of the list 5
How to sort a List (Of T) 5
Data Validation: items in one list relate to items in another 1
Value comparison in sorting a generic list class 3
Sorting a list with tabs 7
Opening Dialog when user presses Enter key in List View 1

Back
Top