Find function for a listbox table in a userform

[Roger on Excel]

[Excel 2003]

I have a listbox in a Userform.

The list box displays a table of chemicals and their properties. It is a
multicolumn listbox and reads from a list called "Materials" which has approx
1000 chemicals in it.

I would like to add a textbox in which a user enters a chemical name and
code searches for that entry in the list, highlighting if it is found and
communicating if it isnt.

In my listbox, the chemicals are in the first column of the displayed table.

Additionally, I would like the user to be able to search by a desriptor
number (called a CAS number) which is in the second column in the listbox.
Each chemical has a unique descriptor number and is often used as alternative
way to search for a chemical.

It would be nice to have a "Find Next" button since sometimes the chemicals
are listed twice in the table under different chemical names, but with the
same CAS number.

Having the option of a wilcard * search option would be an added bonus. Any
other options would be great to have but a basic find ability would be the
priority.

Can anyone help?

 

[joel]

why not use a combobox rather than a text box. You can set the combobox
property RowSource to sheet1!A1:A15 (or equivalent) which is the list of
chemicals. The combo box give you the option of either typing in the
item or selecting the item from the list.

To find an item in the workshett us the following


Chemical = "Oxygen"
set c = sheets("Sheet1").Range("A1:A15").find(what:=Chemical, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find chemical : " & Chemical")
else
foundrow = c.row
end if