Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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" wrote in message oups.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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" wrote in message oups.com... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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" wrote in message ups.com... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
10,000 element listbox possible? what about searching in it?
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" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Multicolumn Listbox and ordinary listbox | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |