10,000 element listbox possible? what about searching in it?

I have the following situation and dont even know which tool to
use...excel? access? other?
I have a list of about 10,000 elements. These are chemical compounds. I
need to create an interface to be able to "assemble" the compounds so
that each compound shows its ingredients.
ex:
Lets say I have (in a huge column)

comp1, comp2, comp3, comp4, comp5, comp5, comp6

Now, I select comp1 to work on it... and then select the ingredients
that makeup comp1

comp1-->comp2
comp3
comp4

Now I select comp2 and work with it...
comp2-->comp4
comp5
comp6

Basically each of the compounds on this list is made up of one or more
compounds on the same list

So I was thinking I should have at least two listboxes. On the left one
I select which compound I would be working on, and then from the list
on the right (with multiselect on) I select all of its ingredients
(both listboxes would how the same columns of data).

Is this at all doable with about 10,000 elements?
I would also like to have a textbox that would work as a search box for
the right side listbox. It should work by simply detecting changes and
doing instant searches....so if I type "c" it would jump to the first C
in the list, but then I add an 'o' (now "co" on the textbox) it would
jump to the first "co" on the list...and so on...by just completing the
word it should jump automatically....is this possible? will it be done
in relatively decent amounts of time or will the 10,000 element list
be so huge it will take minutes to serach?

thanks,
-gguillermo

Hello gguillermo,

Both ListBoxes and ComboBoxes can have a maximum of 32,767 rows. This applies to Windows '95, '98, and Me. Not sure about 2000 and XP, but I think they have higher limits. A better choice for what you want to do is a ComboBox. The ComboBox functions like a TextBox and ListBox. It has a line editior built in as well as the search feature you mention. The built in search is quite fast, and don't think it would but a second or two to do. Also, no additional coding is needed on your part to use these features.

Hope this answers your question,
Leith Ross

ok, I've tried it in Excel using the very basic knowledge I have of vba
(or programming for that matter)....and this is what I found...
the initial filling of the 10,000 element listbox is fast....but the
searches are extremely slow. Is there a better way to do this?
here is the code I have till now (it kind of works...it selects the
element right before the one I am looking for...but regardless of that,
its extremely slow)
-------------------------------------------------------------
Private Sub UserForm_Initialize()
With Sheet1
ListBox1.List = Application.Transpose(.Range(.Range("A2"),_
.Range("A2").End(xlDown)).Value)
ListBox2.List = Application.Transpose(.Range(.Range("A2"),_
.Range("A2").End(xlDown)).Value)
End With
End Sub

Private Sub TextBox1_Change()
i = 0
While TextBox1.Text <> Left(ListBox1.List(i), Len(TextBox1.Value))
ListBox1.ListIndex = i
Wend
End Sub
----------------------------------------------------------------
Any suggestions?
thank you
-gguillermo

I just discovered the fmMatchEntryComplete feature for listboxes ...now
that one works fast, but....yes, there is a but.....I need to have the
text I am typing show up on a text box...and it has to be persistent
(fmMatchEntryComplete starts from blank after a couple of seconds).
I also need for the search to go backwards if I press the backspace
(not having to type the whole thing again....remember my list is made
of chemical compounds....hard stuff to type )
thanks again!
-gguillermo

Me again....
Ok, I just found the perfect example of exactly what I am looking
for....
in Outlook Express on the Newsgroup Subscriptions window....
there is a textbox on the top and a listbox on the bottom...on the
textbox
I type something and the listbox will show only the newsgroups that
include that text ANYWHERE in the name (not just the left side
(beginning)
of the string). I was able to do something like that using a clear
listbox
and an additem with a "for each" loop, but the result is extremely
slow.
In outlook express with 23000 groups it works decently fast....
any idea how I can achieve this with a listbox whent the elements come
from an excel column?
thanks again....hopefully somebody will read all this
-gguillermo

How are your compounds named ? IUPAC names or some other systematic
convention?

Do you need to be able to type wildcards in the textbox and match on
those?

You might start out by first loading all of your items in an array and
loading the listbox from there.
Each time you filter your array you could clear the list and reload
only the matching items.

Other possibility - use a disconnected ADO recordset to hold your
items. This has sort and filter methods which can (I think) work with
wilcards.


Tim.




"gguillermo" <[email protected]> wrote in message
news:[email protected]...
> Me again....
> Ok, I just found the perfect example of exactly what I am looking
> for....
> in Outlook Express on the Newsgroup Subscriptions window....
> there is a textbox on the top and a listbox on the bottom...on the
> textbox
> I type something and the listbox will show only the newsgroups that
> include that text ANYWHERE in the name (not just the left side
> (beginning)
> of the string). I was able to do something like that using a clear
> listbox
> and an additem with a "for each" loop, but the result is extremely
> slow.
> In outlook express with 23000 groups it works decently fast....
> any idea how I can achieve this with a listbox whent the elements
> come
> from an excel column?
> thanks again....hopefully somebody will read all this
> -gguillermo
>

Hi Tim, yes, IUPAC names (in spanish). It would be nice to be able to
type wildcards, but its not a necessity right now. In order of
importance, id say the "features" i need are like this
1) be able to do the same as fmMatchEntryComplete but by typing in a
textbox...i.e. it just takes me to the first element that starts with
those letters...but, if I delete a character it respects what is in the
textbox and searches again (like comboboxes work...but with the visible
listbox at all times)
2) be able to ONLY show the elements that start with those letters
3) be able to ONLY show the elements that contain those letters
4) be able to only show the elements that contain the letters using
wildcards.

If I can get any of these to work I'd be pretty happy....but it has to
be really fast. I was able to get option 2 to work, but its so slow its
ridiculous.

If I do the array option, will that work faster? How would I filter the
array (I am new to programming)...I will search newsgroups to find that
info.
I know ADO recordset refers to something like Access, but I have no
idea how I would get that to work...would I need excel at all or all
within access? or what exactly did you have in mind there?
thank you,
-gguillermo

Use a more powerful technology to perform your searches, such as SQL Server, or MSDE and populate your results listbox with the data you query from your database. Your searches will be much much much faster this way.

See below - should get you started.
I tested this using a listbox and textbox from the control toolbox.
FilterItems is called from the change event of the text box.

To change whether to use wilcards or not alter the indicated line:
right now is is "starts with" and case-insensitive.

Tim.


Option Explicit

Dim v

Sub setArray()
v = sht1.Range("A3:A1002").Value
FilterItems ""
End Sub

Sub FilterItems(sText)

Dim a As Integer, b As Integer, i As Integer
Dim o

a = LBound(v)
b = UBound(v)

Set o = sht1.lstNames
o.Clear

sText = UCase(sText)
For i = a To b
If sText = "" Then
o.AddItem v(i, 1)
Else
If UCase(v(i, 1)) Like sText & "*" Then o.AddItem v(i, 1)
'*****
End If
Next i
End Sub





"gguillermo" <[email protected]> wrote in message
news:[email protected]...
> Hi Tim, yes, IUPAC names (in spanish). It would be nice to be able
> to
> type wildcards, but its not a necessity right now. In order of
> importance, id say the "features" i need are like this
> 1) be able to do the same as fmMatchEntryComplete but by typing in a
> textbox...i.e. it just takes me to the first element that starts
> with
> those letters...but, if I delete a character it respects what is in
> the
> textbox and searches again (like comboboxes work...but with the
> visible
> listbox at all times)
> 2) be able to ONLY show the elements that start with those letters
> 3) be able to ONLY show the elements that contain those letters
> 4) be able to only show the elements that contain the letters using
> wildcards.
>
> If I can get any of these to work I'd be pretty happy....but it has
> to
> be really fast. I was able to get option 2 to work, but its so slow
> its
> ridiculous.
>
> If I do the array option, will that work faster? How would I filter
> the
> array (I am new to programming)...I will search newsgroups to find
> that
> info.
> I know ADO recordset refers to something like Access, but I have no
> idea how I would get that to work...would I need excel at all or all
> within access? or what exactly did you have in mind there?
> thank you,
> -gguillermo
>

thank you so much Tim, you just taught me a few new commands ("Like",
"LBound", "UBound"....they might be simple ones, but I am pretty new to
all of this).

I tried to figure out how to make this work on the UserForm and this is
what I came up with:
-----------------------------------------
Option Explicit
Dim v

Private Sub TextBox1_Change()
FilterItems (TextBox1.Text)
End Sub

Private Sub UserForm_Initialize()
v = Sheet1.Range("A3:A8439").Value
FilterItems ""
End Sub

Sub FilterItems(sText)

Dim a As Integer, b As Integer, i As Integer
Dim o

a = LBound(v)
b = UBound(v)

Set o = Me.ListBox1
o.Clear

sText = UCase(sText)
For i = a To b
If sText = "" Then
o.AddItem v(i, 1)
Else
If UCase(v(i, 1)) Like "*" & sText & "*" Then o.AddItem v(i,
1)
End If
Next i

End Sub
---------------------------------------------
I modified the Like statement to contain, not just begin with and it
worked perfectly.
My only problem right now is speed. The range has 8439 elements in the
test I am doing, and the first 2 or 3 characters I type take over 10
seconds each to update the list..the next characters shorten the list
instantaneously (my machine is P4 1.6GHz with 512MB RAM). Where is the
slowness happening? in filling the listbox? or in searching? Is there
anyway one can optimize this?

Also, how would I change this to two columns from excel into a two
column listbox? I tried columncount=2 and then playing with the
BoundColumn command, but couldnt get it to work. I also tried the
..List(i,2) command but that didnt work either trying to fill the second
column (I need to keep track of the position in excel of the elements I
am working with so I figure I need to use a second column which simply
has row numbers)

thanks again, I really appreciate all your help!!!!
-gguillermo

Since you probably don't really want to perform that kind of "like"
filter on a single character (or maybe even on two) you could change
the code so it only begins to filter after you've typed more than
three characters. Not much point filtering unless its really going to
reduce the number of items significantly. On my PC it was pretty
fast, but noticeable when many items were being added back to the
listbox. My list may also not have been representative of the content
of your range (don't have 10k IUPAC names handy....)

Why do you need to keep track of the position ? Is the list not
unique? It might be easier just to use Find to get back to the
original item in the spreadsheet range, if that's what you need to do.

Tim.





"gguillermo" <[email protected]> wrote in message
news:[email protected]...
> thank you so much Tim, you just taught me a few new commands
> ("Like",
> "LBound", "UBound"....they might be simple ones, but I am pretty new
> to
> all of this).
>
> I tried to figure out how to make this work on the UserForm and this
> is
> what I came up with:
> -----------------------------------------
> Option Explicit
> Dim v
>
> Private Sub TextBox1_Change()
> FilterItems (TextBox1.Text)
> End Sub
>
> Private Sub UserForm_Initialize()
> v = Sheet1.Range("A3:A8439").Value
> FilterItems ""
> End Sub
>
> Sub FilterItems(sText)
>
> Dim a As Integer, b As Integer, i As Integer
> Dim o
>
> a = LBound(v)
> b = UBound(v)
>
> Set o = Me.ListBox1
> o.Clear
>
> sText = UCase(sText)
> For i = a To b
> If sText = "" Then
> o.AddItem v(i, 1)
> Else
> If UCase(v(i, 1)) Like "*" & sText & "*" Then o.AddItem v(i,
> 1)
> End If
> Next i
>
> End Sub
> ---------------------------------------------
> I modified the Like statement to contain, not just begin with and it
> worked perfectly.
> My only problem right now is speed. The range has 8439 elements in
> the
> test I am doing, and the first 2 or 3 characters I type take over 10
> seconds each to update the list..the next characters shorten the
> list
> instantaneously (my machine is P4 1.6GHz with 512MB RAM). Where is
> the
> slowness happening? in filling the listbox? or in searching? Is
> there
> anyway one can optimize this?
>
> Also, how would I change this to two columns from excel into a two
> column listbox? I tried columncount=2 and then playing with the
> BoundColumn command, but couldnt get it to work. I also tried the
> .List(i,2) command but that didnt work either trying to fill the
> second
> column (I need to keep track of the position in excel of the
> elements I
> am working with so I figure I need to use a second column which
> simply
> has row numbers)
>
> thanks again, I really appreciate all your help!!!!
> -gguillermo
>

thats a good idea tim, I'll try to figure out how to get the routine to
work only with 3 characters or more.
The reason I need to track the position is that eventually what I am
trying to do is compose a list of each of the compounds with its
ingredients, and I am thinking it would be easier to keep track of the
ingredients by number than by name (for faster processing in later
stages). For example...if you read my original post about the
ingredients, after I figure all this out, my output for "comp2" (from
original post) woudl be:
2 | comp2 | 4,5,6 (assuming the bars separate cells)
that is, the position of comp2 and the positions of the ingredients
separated by commas.
thank you,
-gguillermo
PS.- For the testing I am not using the iupac list as I dont have it
either...i am using a list with 8500 male and female names I found on
the web.

ok, figured out the minimum length thing with the LEN
command....cool....new command learned.
Now I need to figure out the position...by the way, the intention is
that this info will later be inserted into a database, and that why i
need the position (to use it as a key I guess).
thanks,
-gguillermo

I still think you might find it easier to do a lookup on the original
list (or even the array loaded from the list).

Since you'll only have a few items it should be pretty fast.

Tim


"gguillermo" <[email protected]> wrote in message
news:[email protected]...
> ok, figured out the minimum length thing with the LEN
> command....cool....new command learned.
> Now I need to figure out the position...by the way, the intention is
> that this info will later be inserted into a database, and that why
> i
> need the position (to use it as a key I guess).
> thanks,
> -gguillermo
>

ok, i honestly gotta say that this time I am really lost. I dont
understand how I would be able to do that. LIke I mentioned, the reason
I need to do this is so that when I later export this info to be used
in a database, the position will be used as the key (i guess...i dont
know much about databases).
So the only way I can see I can do this is by keeping at least two
columns in each listbox with one of them being the index...
if you dont mind suggesting and explainng a little more what it is you
have in mind, I would really appreciate it.
thank you again!!!!!
-gguillermo

You have the initial array you loaded from the worksheet. To find the index
of a value just loop through the array and check whether the value matches
the current array position.

Mail me if you want help offline (making the obvious changes to my address).

Tim.

--
Tim Williams
Palo Alto, CA


"gguillermo" <[email protected]> wrote in message
news:[email protected]...
> ok, i honestly gotta say that this time I am really lost. I dont
> understand how I would be able to do that. LIke I mentioned, the reason
> I need to do this is so that when I later export this info to be used
> in a database, the position will be used as the key (i guess...i dont
> know much about databases).
> So the only way I can see I can do this is by keeping at least two
> columns in each listbox with one of them being the index...
> if you dont mind suggesting and explainng a little more what it is you
> have in mind, I would really appreciate it.
> thank you again!!!!!
> -gguillermo
>