Making a list of 100s of items typing narrows search

[Ryan]

I have a list of about 250 food items, in order for someone to choose the
item they have to use the little scroll bar. I want it to kind of be like on
facebook or google were when u start typing it narrows and shows you options
to choose from in the list. Say i wanted it to say apple. When i start
typing app there should be in the list apple and appricott and any other food
that starts with app. Please help me ive been looking for solutions for
about 2 weeks now and this project is due in 2 weeks.

 

[Rick Rothstein]

I assume you are using a ListBox, correct? Where is it located... directly
on the worksheet or on a UserForm? If on the worksheet, where did you get
the ListBox from... the Control Toolbox toolbar or the Forms toolbar? Or are
you using a Validation List? Just so we won't have to "make something up",
where is your food list located at (sheet, column)?

 

[RB Smissaert]

Shouldn't be any problem.
How is your data displayed?
In the sheet or in a listbox in a form or any other than that?

RBS

 

[Gord Dibben]

What type of list?

Data Validation dropdown?

Autocomplete is not supported in DV lists.

See Debra Dalgleish's site for using a combobox in conjunction with the DV
list.

http://www.contextures.on.ca/xlDataVal10.html


Gord Dibben MS Excel MVP

 

[Matthew Herbert]

Ryan,

It will likely take some time to program something that mimics the Google
search filter text box. Do you really need to visibly see all of the results
in the box as you type, or is simply one result as you type sufficient?

Adding a ComboBox to a UserForm will fulfill the latter question. So, you
can do the following (as an example):
- Open VBE
- Insert a UserForm (Insert | UserForm)
- Add a ComboBox to the UserForm

UserForm Code (which assumes your list starts in A1 and is contiguous in
column A):
Private Sub UserForm_Initialize()
Dim varRange As Variant
varRange = Range(Range("A1"), Range("A1").End(xlDown)).Value
Me.ComboBox1.List = varRange
End Sub

Module Code:
Sub ShowForm()
UserForm1.Show
End Sub

Simply run "ShowForm", put the cursor into the ComboBox, and start typing.

Best,

Matthew Herbert