ComboBox filtered on partial match?

  • Thread starter Thread starter Ian Chappel
  • Start date Start date
I

Ian Chappel

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).
 
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.
 
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
 
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
 
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.
 
Back
Top