ComboBox filtered on partial match?
 

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.

ComboBox filtered on partial match?
 

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.

ComboBox filtered on partial match?
 

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.

ComboBox filtered on partial match?
 

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.

ComboBox filtered on partial match?
 

My fault. Filter requires a one dimensional array. Res should be dim'd as
Variant.

Private Sub Combobox1_Change()
Dim rng as Range, varr1 as Variant
set rng =Workbooks("Data.xls").Worksheets("Sheet1").Range( "A1:A9500")
varr1 = rng.Value
varr1 = makeone(varr1)
combobox1.list = Filter(varr1,combobox1.Value,True,vbTextCompare)
End Sub

Private Sub Combobox1_Click()
Dim rng1 as Range, rng as Range
Dim res as Variant
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)
Label1.Caption = rng1.row
Else
Label1.Caption = "No Match"
End if
End Sub

Public Function MakeOne(varr as Variant)
Dim varr2 as Variant, i as Long
Redim varr2(lbound(varr,1) to ubound(varr,1))
for i = lbound(varr,1) to ubound(varr,1)
varr2(i) = varr(i,lbound(varr,2))
Next
MakeOne = varr2
End Function

Make the matchentry property as fmMatchEntryNone and the style as
fmStyledropdownCombo

--
Regards,
Tom Ogilvy


Ian Chappel wrote in message
...
Thanks Tom,

I've tried code, after Declaring rng and rng1 as Range, varr1 as Variant,
and res as String (?).

Both ways though hang (unless I've done something silly!) - the first one
on
the 3rd line, and the second on the line commencing "res =".

Any ideas?

"Tom Ogilvy" wrote in message
...
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.

ComboBox filtered on partial match?
 

Tom

Brilliant! I can't thank you enough - I daren't think how long it would have
taken for me to come up with that.

What really surprises me is how fast it works - virtually instant - even on
my 600MHz laptop, which is usually painfully slow. I alway thought using
Workbook functions was bad, but Match seems to work very well.

"Tom Ogilvy" wrote in message
...
My fault. Filter requires a one dimensional array. Res should be dim'd
as
Variant.

Private Sub Combobox1_Change()
Dim rng as Range, varr1 as Variant
set rng =Workbooks("Data.xls").Worksheets("Sheet1").Range( "A1:A9500")
varr1 = rng.Value
varr1 = makeone(varr1)
combobox1.list = Filter(varr1,combobox1.Value,True,vbTextCompare)
End Sub

Private Sub Combobox1_Click()
Dim rng1 as Range, rng as Range
Dim res as Variant
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)
Label1.Caption = rng1.row
Else
Label1.Caption = "No Match"
End if
End Sub

Public Function MakeOne(varr as Variant)
Dim varr2 as Variant, i as Long
Redim varr2(lbound(varr,1) to ubound(varr,1))
for i = lbound(varr,1) to ubound(varr,1)
varr2(i) = varr(i,lbound(varr,2))
Next
MakeOne = varr2
End Function

Make the matchentry property as fmMatchEntryNone and the style as
fmStyledropdownCombo

--
Regards,
Tom Ogilvy


Ian Chappel wrote in message
...
Thanks Tom,

I've tried code, after Declaring rng and rng1 as Range, varr1 as
Variant,
and res as String (?).

Both ways though hang (unless I've done something silly!) - the first
one
on
the 3rd line, and the second on the line commencing "res =".

Any ideas?

"Tom Ogilvy" wrote in message
...
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.