Solved: Find as you type

[AAM]

I have a long list of names in locked cells. The worksheet is
protected, allowing locked cells to be selected so I can use the
"Find" dialog.

What I'd really like is to be able to select any cell in the list,
start typing, and have the cursor jump to the first cell matching
the typed text as I type. This would be the same behavior that you
see when you type a filename in a Windows folder.

I searched for some kind of Workseet_SelectionChange code to work
from, but haven't found anything.

Any ideas where to start?

TIA,

Andy

[Aussiebear]

As in an Autocomplete function?

[lucas]

I don't know if this can be done in a cell but you can do it with a listbox...see attached example. I know this is not what you asked for but I don't know of a better way.

[AAM]

As in an Autocomplete function?

Sort of, in that it would look for partial matches as you typed. But the idea is to get the cursor to the vicinity of what you are finally looking for, or right to an exact match if you typed enough to be a unique match.

[AAM]

I don't know if this can be done in a cell but you can do it with a listbox...see attached example. I know this is not what you asked for but I don't know of a better way.

That might be a bit better than using the Find dialog. And it's a nice example for me to keep to refer to.

Thanks!

[asingh]

Instead of using the control-F [Find] option..why not build a FORM..with a input box..and here when the users type..as each letter is typed.you can use the text box change property to initiate a search sequence on your spreadhsheet cells.for a holistic match....

Easier to explain than do..Give it a shot.

regards,

asingh

[AAM]

Instead of using the control-F [Find] option..why not build a FORM..with a input box..and here when the users type..as each letter is typed.you can use the text box change property to initiate a search sequence on your spreadhsheet cells.for a holistic match....


The cursor would jump to the first available cell as I typed each letter into the text box?

[asingh]

Yups..that can be done....from where would you begin your search..and where would it end on the spreadsheet....?

[mdmackillop]

This uses a combobox

[lucas]

the userform again. The form is modal so you can start typing if you wish or close the form and start typing.....

[Aussiebear]

Can this only find on the sheet in which the range is found or can it be used to find across a workbook

[AAM]

mdmackillop and lucas -

Both solutions very cool! listbox search.xls is probably a little closer to what I'm looking for, but both of them show me what is possible.

Thanks to you both!

Andy

[SherryO]

Hi - I'm using the code contained in this thread and everything is working fine (thank you), but I need to make an enhacemnt to it and I'm not sure how to. I love the search as you type, but how would one be able to also have the user simply scroll and click? Also how is it possible to have the close button activated (for lack of a better term) when the user types enter. Thank you soo much. Sherry
I should have been more specific. I'm using listbox search.xls

[YellowLabPro]

Sherry,
Mdmackillop's version allows for this. The combbox expands and drag the scroll bar. Does that handle what you require?

[SherryO]

Ok, I see that it does work that way. What confused me is that the choice one makes with the click doesn't show in the text box. How could I get that to happen? Also how would typing enter trigger the close button? Thanks again!

[YellowLabPro]

I don't think it works that way. It is a userform and then an event. Once you get use to it, it will feel natural.
The thing I think would be nice is a fixed button to take the user to back to the findcell box.
Maybe Malcolm can add this enhance the functionality here.

[rory]

Here's a slightly revised version that will enter the listbox values in the text box if selected via the listbox, does a gradual match in the textbox too and has the Close button set to default so pressing enter closes the form.
HTH
Rory