I want to fill a Combobox with items from a spreadsheet column (9500 cells)
where there is an "in-string" match to the data I enter in the Combo-box.
For example, if I were to type the word "disk" into the Combobox, the
Combobox would display the following items only from my 9500 items:

hard disk
harddisk
disk-drive
diskette

I must match one of the items, and ideally also I would like to retrieve the
row number of my final choice for further use (bound column).

I have a few ideas like filling an array using the Change event, but I'm
wondering if there's an easier way by maybe using a RecordSet?

Hope someone can help! Thanks.

You want to type "disk" in the combobox, then have the combobox List only
contain items that contain the word disk, then you want to select one of
these and have that be the value of the combobox.

If you receive the row number is it the row number against the original data
(1 to 9500) or against the culled data (a list with each item containing the
word disk)?

You could use an autofilter to filter your data or look at the filter
command (in xl2000 or later).


--
Regards,
Tom Ogilvy



Ian Chappel wrote in message
...
I want to fill a Combobox with items from a spreadsheet column (9500
cells)
where there is an "in-string" match to the data I enter in the Combo-box.
For example, if I were to type the word "disk" into the Combobox, the
Combobox would display the following items only from my 9500 items:

hard disk
harddisk
disk-drive
diskette

I must match one of the items, and ideally also I would like to retrieve
the
row number of my final choice for further use (bound column).

I have a few ideas like filling an array using the Change event, but I'm
wondering if there's an easier way by maybe using a RecordSet?

Hope someone can help! Thanks.

Thanks Tom

The row number returned would be against the original data, i.e. 1 to 9500.

I realise I could do this quite simply on the actual sheet using Autofilter
etc., but I do need the filtered data returned as my choices in the
Combobox. The Combobox is in another workbook, although they'll both be open
at the same time. I guess the problem would not be much different if the
Combobox was in a user form.

"Tom Ogilvy" wrote in message
...
You want to type "disk" in the combobox, then have the combobox List only
contain items that contain the word disk, then you want to select one of
these and have that be the value of the combobox.

If you receive the row number is it the row number against the original
data
(1 to 9500) or against the culled data (a list with each item containing
the
word disk)?

You could use an autofilter to filter your data or look at the filter
command (in xl2000 or later).


--
Regards,
Tom Ogilvy



Ian Chappel wrote in message
...
I want to fill a Combobox with items from a spreadsheet column (9500
cells)
where there is an "in-string" match to the data I enter in the
Combo-box.
For example, if I were to type the word "disk" into the Combobox, the
Combobox would display the following items only from my 9500 items:

hard disk
harddisk
disk-drive
diskette

I must match one of the items, and ideally also I would like to retrieve
the
row number of my final choice for further use (bound column).

I have a few ideas like filling an array using the Change event, but I'm
wondering if there's an easier way by maybe using a RecordSet?

Hope someone can help! Thanks.

assuming xl2000 or later

set rng =Workbooks("Data.xls").Worksheets("Sheet1").Range( "A1:A9500")
varr1 = rng.Value
combobox1.list = Filter(varr1,combobox1.Value,True,vbTextCompare)




set rng =Workbooks("Data.xls").Worksheets("Sheet1").Range( "A1:A9500")
res = Application.Match(combobox1.Value, rng,0)
if not iserror(res) then
set rng1 = rng(res)
msgbox rng1.row
Else
msgbox "No Match????"
End if

--
Regards,
Tom Ogilvy


Ian Chappel wrote in message
...
Thanks Tom

The row number returned would be against the original data, i.e. 1 to
9500.

I realise I could do this quite simply on the actual sheet using
Autofilter
etc., but I do need the filtered data returned as my choices in the
Combobox. The Combobox is in another workbook, although they'll both be
open
at the same time. I guess the problem would not be much different if the
Combobox was in a user form.

"Tom Ogilvy" wrote in message
...
You want to type "disk" in the combobox, then have the combobox List
only
contain items that contain the word disk, then you want to select one of
these and have that be the value of the combobox.

If you receive the row number is it the row number against the original
data
(1 to 9500) or against the culled data (a list with each item containing
the
word disk)?

You could use an autofilter to filter your data or look at the filter
command (in xl2000 or later).


--
Regards,
Tom Ogilvy



Ian Chappel wrote in message
...
I want to fill a Combobox with items from a spreadsheet column (9500
cells)
where there is an "in-string" match to the data I enter in the
Combo-box.
For example, if I were to type the word "disk" into the Combobox, the
Combobox would display the following items only from my 9500 items:

hard disk
harddisk
disk-drive
diskette

I must match one of the items, and ideally also I would like to
retrieve
the
row number of my final choice for further use (bound column).

I have a few ideas like filling an array using the Change event, but
I'm
wondering if there's an easier way by maybe using a RecordSet?

Hope someone can help! Thanks.