Fill Array From Combobox

Combobox Fill In
how can you make a combobox fill in with the records from an access data base?
example:when you have A database with table students and you wanne add 1 new you can only add 1 to a clas who realy is, and i want a combobox with the classes .

---

Fill A Combobox
Hi all,

I wanna fill a combobox with a range of cells that contain some characters.
Some of them are subscript or superscript.
That I want to do, is to fill the combobox preserving the format of the characters in the original cells.
Now I am using the following code to fill my combobox named ZoneList but I am sure it misses something for that I want.

For i = 1 To 10 Step 1
ZoneList.AddItem Sheets("Feuil1").Cells(i, 1).Format
Next i

Thank for your help
Defré.

---

Fill Combobox
Hi,

When I load the form, I want to load all zipcode table into the cbx but I want to leave the zip code of the first client selected. And when I change client I want to change the cbx text value.

any idea?

thanks

---

Fill 2nd Combobox
I have the following code for my fost combobox; CODEPrivate Sub Userform_Initialize()

    Dim ws As Worksheet
    
    Set ws = Worksheets("Employees")
    Set MyRange = ws.Range("A2", ws.Range("A150").End(xlUp))
    
With Me.Employee
.RowSource = ws.Name & "!" & MyRange.Address
.ListIndex = 0
End With

End Sub

---

How To Fill Combobox ?
What I try to do is:
1. I have a form with many "combobox"
2. I have a text file with two strings at each line, the first is the "combobox" name, and the second is the value that I would like to add to this particular combobox.
3. I read each line from that text file
What I am asking is: how do I "additem" to the combo which name is the first string and the text that should be added is the second string.

---

Fill More Then 0ne Combobox In A Userform
Hello.

I'd like to fill 4 comboboxes on a userform. The problem is i don't know how to loop through the 4 combobox object.

Thanx in advance.

Werner

my code:

Code:
Private Sub UserForm_Activate()

Dim cmb As ComboBox
Dim cmbo() As ComboBox

fileGrootBoek = "C:XXXXXurenverantwoordingGrootboek.xls"
qry = "SELECT nr, grootboek FROM `Grootboek$`"

'get a handle on all comboboxes
For Each cmb In frmGrootBoek
Set cmbo(i) = cmb
Next

'counters
i = 0
j = 0

'Fill form with values from excel sheet
Call test

'Execute query
Set result = Gegevens.QrySelect(fileGrootBoek, qry)

'Fill comboboxes wich are named: cmbGB1, cmbGB2, cmbGB3 and cmbGB4
'First empty combobox
For Each cmb In frmGrootBoek

cmbo(j).Clear

'code to fill combobox
Do While (result.EOF = False)

cmbo(j).AddItem (result.Fields(1))
cmbo(j).List(j, 1) = result.Fields(0)
j = j + 1
result.MoveNext

Loop

Next

'close recordset
result.Close
Set result = Nothing

End Sub

---

Fill Combobox From Listview
I've got the Problem that I need to "copy" a column out of a listview-field into a combobox.
It's working that far, but I'm doing it in two steps. At first I copy the values from the listview to a worksheet and in the second step I fill the combobox with the help of Rowsource.

Is there any posibility do this easier in maybe one single step?

EDIT: I've found the solution... was just to blind to see it directly. I'm just getting started with VBA programming

---

Fill 23 Combobox With The Same Info
hi!, look i have a form that contains 23 combobox, all with the same info (blaxk, white, ...), a now i can do it in like this


Private Sub UserForm_Initialize()

ComboBox1.AddItem "black"
ComboBox1.AddItem "white"
ComboBox1.Value = "choose"

ComboBox2.AddItem "black"
ComboBox2.AddItem "white"
ComboBox2.Value = "choose"

ComboBox3.AddItem "black"
ComboBox3.AddItem "white"
ComboBox3.Value = "choose"
.
.
and so , and so, but come on, there ir other way to do this. plz can u help me

---

Fill Combobox With Query
I have filled the values of my combo box with a query from an access database, and have been unable to figure out how to let the combo box allow other values typed in. If it is not included in the query, I cannot type the value in. I have played around with the properties with no luck.

---

Can I Automatically Fill In A Combobox?!?!
I have a combobox and the list comes from an access file. I want to have it so when the user tabs onto the combobox and starts typing the closest match would come up with a blue backround and if the user tabs over again it will fill the combobox. I know I have to do it in the change event and change the backround color but I'm just need to be zapped with a defribulator to my brain..... any ideas I'll try to figure it out until then... but any help will be appreciated

---

Use ComboBox To Fill In Other Fields?
I either don't understand ComboBoxes or I just don't know what they can't do.

I have a ComboBox (cboOdds) with a prefilled list of 8 options that will never change (I am using Style "2 - Dropdown List"). I want to be able to select one of the items in the list and have this immediately fill a text box (txtOdds) with text connected to that item in the list. If I then select a different choice from the ComboBox, the information in the text box will be changed to the new information connected to the second item and so on.

In short, turning the ComboBox into the trigger for a text box auto-fill.

---

Fill Combobox With Records...
assuming i have a mdb Mymdb in a server dir \myservermydirMymdb.mdb in this mdb have table_01 with field Test_01

i would want to fill the combobox1 with the records in Table_01...
Naturally fill the combobox1 wothout balnk records...
how can?
Tks.

---

Using SQL To Fill A ComboBox [SOLVED]
Hi all!

I'm using this code below to add the content of the first column (CHP) of my database to a ComboBox. I'm not a sql-Expert, so I would like to ask how I add the first and the second column (NCHP) to this ComboBox (in one row: Combo1.Additem Column1 & Coulmn2).



VB Code:
Dim db As Database        Dim rs As Recordset        Set db = OpenDatabase(TOURENWF.F4UG5T02.Text)        Set rs = db.OpenRecordset("SELECT CHP, Count(CHP) As InitCount FROM Tab1 GROUP BY CHP")        Do Until rs.EOF            Combo1.AddItem rs!CHP             rs.MoveNext        Loop        rs.Close        db.Close


Hope u understand my problem!
thx, Matt

---

Fill A ComboBox With Fonts
Hi all,

hoe can I fill a ComboBox with all Fonts that are available at the system ??

CU
Kreuzfeld

---

Fill Combobox From Database
I have an Access dataBase and need to fill a ComboBox with the contents of a specific field, I'm just trying out database stuff so be gentle with me.

I can get all the field into textbox's and advance through them add, delet ect but I've been up since 3.30
this morning thinking obout it.

The DataBase is called Mailing and the filled is called Email. I know Boring names but what the Hell.

---

Fill A Combobox From A Textfile?
I want to fill a combobox with each line from a txt file. For example the text in mytext.txt looks like:

Adress 1
Adress 2
Adress 3


I want to add this 3 lines in to my combobox, how?

---

Database ? - Fill A ComboBox
How would I go About using ADO to fill a combo box...

I am using this...(but somehow i think it could be done a little better )

rs.MoveFirst
Do While Not rs.EOF
cmbID.AddItem rs!reqID
rs.MoveNext
Loop
rs.MoveFirst


reqID is the field name...

---

Creating Sub To Fill Combobox. Help
Hi all,

I have a sub which I want to call to fill a combobox. But it doesn't work. Here's the code:
---------------------------
Sub AddListItems(cmbname As ComboBox)

Dim sTemp As String
cmbname.Clear

Open (App.Path & "list.txt") For Input As #1
While Not EOF(1)
Line Input #1, sTemp
cmbname.AddItem sTemp
Wend
Close #1

End Sub
----------------------------

For example when the combobox I want to fill is named cmbStuff, I would call it AddListItems (cmbStuff)

It should work right? But it doesn't. It gives me type mismatch.

If I replace the line
"Sub AddListItems(cmbname As ComboBox)"
with
"Sub AddListItems"
and change all the "cmbname" with "cmbStuff" in the above code, and call the simply by AddListItems, it works FINE.

Where is my mistake? Thanks in advance for any help.

Martin

---

How To Fill 3 Combobox With The Index....
assuming i have combobox1, combobox2, combobox3 ho to fill( in initialize form) all combobox with unique item in this mode:

combobox1 column D
combobox2 column H
combobox3 column L

and use the cmobobox2 similar index...

Example:

if i select in comobox2=SEGNO SALDO ANOMALO
auto fill comobox1=150001059999
auto fill combobx3=4500

if i select in comobox2=SEGNO IMPORTO ANOMALO
auto fill comobox1=150008040030
auto fill combobx3=4500

ecc...

in effect auto fill other combobox based combobox2






Edited by - Geof on 2/24/2007 7:50:54 AM

---

How To Fill A Combobox With Available DSN Entries?
How to fill a combobox with available DSN entries?


Initiative is to success what a lighted match is to a candle...

---

Combobox List Fill Range
I have a spreadsheet with three comboboxes. The first combobox lists whether or not the wood beam is sawn or glue laminated (for example). The second combobox lists the species of the wood (oak, cedar, etc.). The third combobox lists the available grade (stud, utility, etc.). All 3 comboboxes are based on a fill range within the spreadsheet and all three are written with VBA code. My hangup is that there is a situation when the wood beam size is based on a different fill range.
Is there a way to get excel to recognize something along the lines of:

If combobox2.text<>"Southern Pine" or "Mixed Southern Pine" then
combobox6.listfillrange="AI2:AI9"

I know this can be done with data validation but I would prefer to use VBA.

---

Fill A Combobox Via VBA With Directory Names
I was searching in the archive of this forum, but i never saw a good answer to my question.
What i like to have is, that when my sheet is loaded, automatically fills a ComboBox (/drop down list) with all directory names, in a specific directory (e.g. look in c: est and if there are directorys in it, show them in the combobox).

I am aware of the excell method, to place the founded values in a cell, and with a linked cell and linked range, i can show the values in the list. But i don't want to do that, i want that vba fills that combobox.
a>is that possible
b>is it possible to do that on load.
The object combobox is never been accepted as an object, except in the sheet code itself.
anyone?

---

Fill Combobox With Results Of Findnext
Hello,

I have a number of sheets, all containing football team names.
I run a find macro based on a a text string; this only finds exact matches. If no exact matches occur, I need a combobox populated with all the results of another find search; this time an (xlpart) search. All the nearest matches need to be shown, with an option to pick the correct one. e.g Let's say I search for "Man"; I would like a box populated with "Manchester United", "Manchester City", "Mansfeild", etc.

If anyone has any code, or previous experience of such a problem I would be most grateful.

Cheers

H

---

Fill ComboBox With 'drive-like' Info
I read most of threads pertaining to using code to select folders versus files. Specifically using the API calls BrowseForFolder and the CommonDialog. Below is an image I'd like to recreate in code. Specifically, filling the ComboBox with the drive information. I believe if I could get that far I could get the rest.

http://www.bluerivercofc.org/images/combobox.jpg

Any assistant would be greatly appreciated.

--Gary

---

Auto Expand/Fill In ComboBox
I am looking for a way to "auto fill" a combo box as the user types.
For example, if the combobox contains "ab, abra, abraca", "ab" should appear as soon as "a" is typed, then "abra" as soon as the "br" is added, etc.

MSAccess has this feature in the ComboBox Properties called "Auto Expand." I found in a reference book that Style = 1-Simple Combo is supposed to do that (called Extended Matching) in VB6. However, it only works if the user presses the down key after typing the first few letters.

Finally, I found some code at http://www.xtremevbtalk.com/show...ended+matching which is supposed to do that.

Is there any easy way/setup to achieve this?

Thanks!
hervé Chain
Houston, Texas

---

Auto Completen ComboBox + DB Fill...
Ok looking for the control for an autofill combobox

I am accessing a database doing so

VB Code:
Private Sub searchLname()Set rs = New ADODB.RecordsetDim SQL As StringSQL = "Select Lname from userTable"[b]rs.Open SQL[/b] [i]'choking here[/i]Do While Not rs.EOFuComboLname.AddItem rs!Lnamers.MoveNextLoopEnd Sub


Runtime error COnnection is either closed or invalid

Is my open line wrong?

My end result should be to fill this combo box with all the last names of people, and when the user is searching for a user it should autocomplete based on that list in the combobox.

Thanks

---

Fill Combobox With Data And 1 Option All
Hi

How Do I fill a combobox with the data from query and an option all ?

thank you in advance

---

Fill TextBox Linked To ComboBox (Access)
It's been awhile since I've used Access, so bear with me.
I have a combobox that is linked to Column A in Table A. When the user selects something from the combobox, I want the associated information in Column B in Table A to fill a TextBox. Also, if I make changes to the information in the TextBox, I want that to replace what was there. Is this possible (I'm assuming it is)? Is it done through a Macro or VBA (or other???)? Thanks in advance!!

---

With ComboBox Changing Fill Color Ellipse
Hello,

I am a starting VB programmer and i've got a little problem.

My goal at this moment is maybe very simple, but I just don't have the knowledge (yet) to know to solve this.

I want to change the color of my ellipse by selecting a value from a ComboBox2

ComboBox2 contains the names of the colors like Firebrick, Red, bla bla.

The problem is indicated at the bottom of my script. I've tried some things, which should be logical for me, but the don't work.


Code:
Private cCircleColor = Color.Brown 'my ellipse color var

Sub flips(Optional ByVal e As System.Windows.Forms.PaintEventArgs = Nothing)
Static varE As System.Windows.Forms.PaintEventArgs
If Not e Is Nothing Then
varE = e
End If

varE.Graphics.FillEllipse(New SolidBrush(cCircleColor), iX, iY, 25, 25)
End Sub 'flips

Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
cCircleColor = Color.(ComboBox2.SelectedItem)
' ^^ This is the part
Call flips()
End Sub
Hope someone can help me 'ere

---

Fill TextBox Linked To ComboBox (Access)
This is a repost from a posting I did in the VBA forum. I wasn't sure if I needed to ask it there or here...sorry.

It's been awhile since I've used Access, so bear with me.
I have a combobox that is linked to Column A in Table A. When the user selects something from the combobox, I want the associated information in Column B in Table A to fill a TextBox. Also, if I make changes to the information in the TextBox, I want that to replace what was there. Is this possible (I'm assuming it is)? Is it done through a Macro or VBA (or other???)? Thanks in advance!!

---

[solved] Fill Combobox With Database Values
Hi,

I have the following tables in a Access 2000 database:

tab_employee
---------------------
ID
name
department (number)



tab_department
---------------------
ID
department


Now I have created the following recordsets:


Code:
rsEmployee = "
SELECT * FROM tab_employee, tab_department
WHERE tab_Mitarbeiter.department = tab_department.ID"

rsDepartment = "SELECT * FROM tab_department"


Now I fill a a formular of Employees with my rsEmployee and put a Datacombobox with the Department on the formular:


Code:
Set dcombo.RowSource = rsDepartment
dcombo.ListField = "Department"


I get a problem when I´d like to show the department of an employee in the Datacombobox:


Code:
If Not IsNull(rsEmployee.Fields("department").Value) Then dcombo.Text = rsEmployee.Fields("department").Value


Because I can´t readout rsEmployee.Fields("department"). But when I test my SQL statement of the rsEmployee in Access it runs...Can anybody help me?

Thank you for all considerations,

Andy

---

Module Level Combobox Fill Routine
Hi all. I have a quick question. I think this should be simple, but I can't get it to work. Throughout my app, I have a number of combo boxes that display all of the US states. The states are loaded from a database, and I'm trying to write a module level procedure that I could call to fill the state combo boxes when I need to, but I cant' get it to work. Any suggestions?

Thanks

FLL

---

Fill In Textbox Based On A Combobox Selection
I am connecting to an Access DB using ADODB from a VB6 application. I can connect to the DB, and create recordsets. What I need a bit of help with is:

I want a text box to automatically be filled in with a field (field=Location) after the user selects an item from a dropdown box (field=iPAQ). The two fields are in the same table (table=iPAQs) so the two fields would be from the same record. I just want the text box to be automatically filled in.

Here is how I make the recordset for the iPAQs table:
Code:
    'Loads iPAQs table into rsiPAQs
Set rsiPAQs = New ADODB.Recordset
With rsiPAQs
    .ActiveConnection = cnMHS
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockPessimistic
    .Source = "SELECT * FROM iPAQs"
    .Open
End With


Then I load the records from the iPAQ field into a combo box (CboiPAQ) using:
Code:
    'Fill the iPAQ Combobox
Do Until rsiPAQs.EOF
    CboiPAQ.AddItem rsiPAQs!iPAQ
    rsiPAQs.MoveNext
Loop


I hope I explained that all OK.
Thanks for any help or pointers anyone can provide!

Ray



Edited by - rjholtz on 6/1/2005 7:22:14 AM

---

(solved) Fill Combobox Based Sheet...
I have a form with 4 tab named with the sheets in workbook...
Now how to fill if i click on tab CORPORATE the combobox1 with the data in sheet CORPORATE with the jlon of column A " - " & " colum B &" - " column C
example:

  OI26710    - ARENA DOMENICO - RESP1
  OI29651    - CALCAGNILE ETTORE - RESP1
ecc...


... and combobox2
  
with the data in sheet RETA__POE with the jlon of column D " - " & " colum E &" - " column F
example:

OI32227- CARILLO VINCENZO - RESP2
OI29651- CALCAGNILE ETTORE - RESP2

ecc...

naturally if i click on tab RETA__POE use the value into this sheet t RETA__POE to fill the 2 combobox...

hope you understand me...



Edited by - luca91 on 12/28/2006 4:40:05 AM

---

Fill A Combobox From Table In Mysql Database
Hi,
I'm sure this question has been asked before but I couldn't find an occurence of it.

I've got 2 comboboxes on a form and I'd like to fill them from a table in a mysql database. Any advice on how this should be done?

Thanks,

Mag2

---

Fill An Array
I think I've been staring at numbers too long because I can't seem to figure out an efficient way of doing this:

Create an array(1 to 8) where each element of the array is a unique random number between 1 and 9

Any help is appreciated

---

How Do I Fill An Array?
I want to fill a hoz. array with variables. Each row will have 8 variables as input and I don't know how many rows I will end up with.

I don't know where to start. Thanks

---

How Do I Fill An Array?
I want to create a hoz. array with input from 8 variables in each row and I don't know how many rows I will have.

Thanks

---

Fill Array With Range
Code:
Dim MLIST() As Variant
Dim lastRow As Long
Dim I As Long

Sheets("Param").Activate
lastRow = Sheets("Param").Range("b65535").End(xlUp).Row
ReDim MLIST(1 To lastRow)
With Sheets("Param")
Let MLIST = .Range("b1:b" & lastRow).Value
End With

Debug.Print MLIST(1)
Does anyone see why I should be getting a "subscript out of range" message
when I try to access values from the array

---

How To Fill Array From A DataBase With ADO?
I have 20 Array Labels

---

How Do I Fill An Array With Words?
How do I fill an array?

Dim varArray(500) As Variant
varArray()=("the", "word"....)

I have tons of words to store into it. I know its not good to hard code values but I need to do it that way. Right now I am doing it this way and its not the best way to do it.

varArray(0)="the"
varArray(1)="word"
...

---

Fill An Array In One Operation
Hello!
Is it possible to fill an array with one line och code?
For instance, if I want an array of strings with the days of the week, I could do it this way:

Dim week(7) as String
week(1) = "Monday"
week(2) = "Tuesday"
week(3) = "Wednesday"
...
week(7) = "Sunday"


But there must be a better way.
Some sort of
Dim week(7) as String
week = "Monday";"Tuesday";"Wednesday"..."Sunday"
or something.

It's possible in C++, allthough I've forgotten how to do it right now, so it must be a way.
Thanks for your help!
Pentax

---

How To Fill Array At Design Time?
I'm trying to preload an array with values at design time to store some data in it. I can't use a loop, so I have to store each element at a time.

The question is:
Can I fill the whole array at once something like this? And how?

Code:
myarray() = {1, 3, 16, 32, 33, 444, 21, 31, 2, .... , 399}


OR

Do I have to go one by one like so

Code:
myarray(0) = 1
myarray(1) = 3
...
myarray(99) = 399

---

Adding Text Fill To An Array
im new to VB6 and i am having trouble writing the code to add a text file to an array. The text file contains differing data types so i'm trying to use a User defined dataType as well. For example a line in the text file contains a usernumber a name an address phone no and monetary value. once in the array i want it to display the contents of the array on the form so that i can see it working.
Can someone help please

---

Using Lines Out Of A .txt File To Fill An Array
Hi,

I need a snippet of code for VB6 that,
reads all the lines from a specific .txt file (10 lines in total)
puts the 10 lines into an array, and picks one and displays it at random

i would appreciate ANY help, thanks... heres what i have so far (its really lame)

Dim txtmsg() As String
Dim MOTD As String
Dim rand As Integer



Open "MOTD.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, txtmsg

Loop
Close #1
For rand = 1 To 10
rand = Int(Rnd * 10) + 1
MOTD = txtmsg(rand)
Exit For
Next

txtOutput.Text = txtOutput.Text & MOTD & vbCrLf & vbCrLf

---

How To Split A Text Document And Fill An Array
As the title suggests. I have a text document with aircraft names and dry operating fuel weights(its for a fuel calculate program) and i need to load all the names into a listbox and all the figures into an array

The information is setup like this

Aircraftname;dry operating weight
Aircraftname;dry operating weight
(BTW this list goes on for a long time but you get the idea!!!!)

Thanks for any help.

---

Fill Array With Month Names? *Answered*
Is there an easy way to fill an array or populate a control with the names of the months other than spelling them all out explicity?

There just has to be a cleaner/easier way to do this:


VB Code:
cboMonths.AddItem "January"cboMonths.AddItem "February"cboMonths.AddItem "March"cboMonths.AddItem "April"cboMonths.AddItem "May"cboMonths.AddItem "June"cboMonths.AddItem "July"cboMonths.AddItem "August"cboMonths.AddItem "September"cboMonths.AddItem "October"cboMonths.AddItem "November"cboMonths.AddItem "December"  montharr(0) = "January"montharr(1) = "February"montharr(2) = "March"montharr(3) = "April"montharr(4) = "May"montharr(5) = "June"montharr(6) = "July"montharr(7) = "August"montharr(8) = "September"montharr(9) = "October"montharr(10) = "November"montharr(11) = "December"

---

How Do I Fill An Array With Specific Numbers (loaded From A Text File)?
I want to create an array of 100 elements which will store a series of both positive + negative Single and Integer numbers. When the program starts, each element in the array needs to be filled with a specific value (which is assigned directly from a text file). When the program is running, I will need to be able to change the value of each element in the array using different math operators. The array must be accessible throughout the program. I also need to save the new array back to a (another) text file when the program finishes.

Let's say the first 5 elements (of 100) should be as follows:
-5, 0, 6.2, 1056, -17.4

(a) How do I create an array solely for use with numercial data?
(b) How do I fill that array with specific values loaded from a text file (let's assume each value in the text file is separated by a comma)?
(c) How do I easily update/change an element within the array after it has been loaded?
(d) How do I write the updated array to a new text file?

I'm very new to this, so your comments and assistance would be very much appreciated.

Cheers

---

Filtering Strings With StrComp (+ Poss Using RefEdit To Fill An Array &amp;/or Listbox?)
Hi,

I've been going around in circles with a new code for the last few days but each time I find an example for part of it online, some or all of the code stalls. Let me tell you what I'm aiming for and then I'll show you the mess I'm in, in case anyone can help...

Right now I've got a (working!) module that copies Column A of a .txt file into a new workbook (adding extra sheets as necessary), trims any leading spaces from the strings, and then saves everything.
Ideally, once the data's loaded into excel, the macro would open a userform that lets users choose strings (from cells in the new sheets - perhaps via RefEdit) that are then used as a sort of template for a Find or Filter-type command using StrComp. You see, I've got a repeating pattern of similar strings (e.g. GEI_W1, HUM_W1_s1, GEI_W2, HUM_W2 etc - the first 5 'letters' don't change btw repetitions) and I'd like to find any instances of cells containing strings that match the basic pattern(s) selected. For example, if I used the RefEdit (or multiline textbox) to select the "GEI_W" & "HUM_W" patterns, then I'd want the macro to search through Column A in every sheet in my new workbook for any cells that contain a string matching either pattern. Any matching strings would then be copied into a new sheet ("Summary"), preferably so that all the GEI_W's are in column A and the HUM_W's are in column B.
I've actually got the code to do the matching procedure but only if there's just 1 search string OR if they're 'hard-coded' into the macro itself, not to mention the fact that it searches across WHOLE strings, not the first 5 letters (meaning that both "GEI_W4_s2" (right) & "BOB_has_GEI_W" (wrong) would be found & copied). And I can't get the macro to accept more than one user-selected string, particularly if I try to use RefEdit so that users don't have to memorise the strings they want ahead of time..

Man, I know that's a pretty tangled description, but I hope someone out there can understand nonetheless. Basically, I just want to filter column A (across >1 sheet) for certain strings/patterns and then copy any matching strings/cells to a new sheet, preferably while sorting them into columns based on the first few letters in the string. This doesn't need to be super speedy since there won't be toooo much to look for or too many sheets to check, no worries. I've even resorted to trying a ton of nested loops, but that didn't work either.

The spot where I'm the most stuck is in transferring the contents of the listbox into an array. I found an example of something similar online but, b/c I'm using excel VBA instead of VB or .NET, some of the commands won't work & I can't find viable alternatives..

Anyhoo, here's the working code:
Code:
'Assume you've got a workbook with strings & blanks in column A, for Sheets 1-3.
Sub CopyStringToNewSheet()
Dim FirstAddress As String, MyArr As Variant, Rng As Range, Rcount As Long, I As Long, AllCaps As String
Dim WS As Worksheet
Application.ScreenUpdating = False
MyArr = Array("GEI_W", "HUM_W") 'searches the whole string, not just its start
ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Summary"
Rcount = 0
For Each WS In ActiveWorkbook.Worksheets()
If WS.Name <> "Summary" Then
With WS.Range("$A:$A")
For I = LBound(MyArr) To UBound(MyArr)
Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rcount = Rcount + 1
Sheets("Summary").Range("A" & Rcount).Value = Rng.Value
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
Next I
End With
End If
Next WS
Application.ScreenUpdating = True
End Sub
I know I could use Left$(string,5) to search only the beginning of each string but so far the above code won't accept it b/c it uses .Find instead of a series of loops & ActiveCells.
The other part of this code is even worse. It assumes your userform has a RefEdit control, listbox, commandbutton1 (to move entries from RefEdit to listbox, b/c I couldn't get that to be an automatic event) & commandbutton2 (to theoretically start the filtering process):
Code:
'most of this is based on a code from [url]http://www.xtremecomp.com/tips/xttip8.htm[/url],
'but the UserForm_Initialize protocol doesn't work in VBA!!
Dim strArray() As String
Const EM_GETLINECOUNT = &HBA
Const EM_LINELENGTH = &HC1
Const EM_LINEINDEX = &HBB
'
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal Hwnd As Variant, _
ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
'
Public Sub UserForm_Initialize()
' Declare local variables to keep track of cursor.
Dim intLineStart As Integer, intLineLength As Integer, lRet As Long
' Initialize the Array.
ReDim strArray(1)
' Loop until all lines in the text box have been read.
For lRet = SendMessage(Me.Hwnd, EM_GETLINECOUNT, 0&, 0&) To 1 Step -1
' Set cursor to next line to read.
ListBox1.SelStart = intLineStart
' Determine the length of this line.
intLineLength = SendMessage(Me.Hwnd, EM_LINELENGTH, ListBox1.SelStart, 0&)
' Select the text of this line.
ListBox1.SelLength = intLineLength
' Copy the selected text into the Array at the appropriate index.
strArray(UBound(strArray) - 1) = ListBox1.SelText
' Increase the size of the Array to handle the next line.
ReDim Preserve strArray(UBound(strArray) + 1)
' Set the line counter to point at the next line
intLineStart = intLineStart + intLineLength
Next
End Sub
'
Public Sub CommandButton1_Click()
Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Array(Range(Me.RefEdit1).Value)
For Ctr = LBound(MyArray) To UBound(MyArray)
UserForm1.ListBox1.AddItem MyArray(Ctr)
Next
End Sub
Does anyone have ANY suggestions for ANY part of this??? I'm driving myself crazy.
I hope that somebody, anybody, can help me get some of this to work....

---