Auto Complete data validation list

[YasserKhalil]

Hello everybody
I need the validation list to be flexible when typing any part of the text . To enable the auto complete feature ..
I found a formula that do that for the first letters only ..
What I want: to be able to type any part of the text and to find quickly the option from the list
I hope it is clear

[davesexcel]

What's the formula and how would you use it?

[YasserKhalil]

The formula is

Please Login or Register  to view this content.

Where MyList is the list on names and C10 is the cell which contains the validation List

[Winon]

Hello YasserKhalil,

If I understand your request correctly, I don't think it is possible to do what you want, without maybe quite a bit of vba.

Regards.

[Debraj Roy]

Check this one.. perfect combination of VBA & Formula..

https://dl.dropboxusercontent.com/u/...%20Google.xlsm

Search Combo like Google.xlsm

[Winon]

@ Debraj Roy,

I would love to see that, but the link you have provided, does not seem to work on my side.

[Debraj Roy]

In hurry.. i pasted External link..

Now in forum too..

[YasserKhalil]

Hello Mr. Debraj Roy
Really fantastic workbook
This what I want exactly
But how can I do that for Validation list ?
I attach the file again and I added a sheet named (Sample) in range(A1:A10) I did make a validation list .. I want when typing make use of auto complete feature of the comboBox in the file..
I don't know how to do that

[Winon]

@ Debraj Roy,

Yes, that is quite something, thank you for sharing!

Regards.

[Winon]

Hi YasserKhalil,

Just see the ComboBox as your "Dropdown", and whatever you click on in the ComboBox will appear in Cell C2, to replicate what you would want the "Dropdown" to finally display.

Regards.

[YasserKhalil]

Thanks sir
But I want to apply that for a range("A1:A50") for example and in the sample it is just related to C2 ..Just one cell not a range

[Winon]

Hi YasserKhalil,

Please upload a sample Workbook with how you have things set up, and I shall see if I can help in making it easier for you.

[YasserKhalil]

See post #8 Mr. Winon
I added a sheet named Sample and I want to apply auto complete feature to the range("A1:A10")
Thanks advanced

[YasserKhalil]

I want to apply auto complete feature to the range("A1:A10")
Is it possible or not to combine the combobox to my range?

[davesexcel]

Autocomplete does not work for data validation dropdown, only for comboboxes.

[YasserKhalil]

I know that Mr. davesexcel
I want to make use of this feature of combobox and combine it to validation list if poosible..
Any idea about that?

[YasserKhalil]

Any idea about this? Will I stop thinking about it?

[Debraj Roy]

Just an Idea..

On Worksheet_SelectionChange Event, place a Dynamic ActiveX ComboBox on the cell, with same Height and width of the target cell.
Place code to check > Dropdown and Validation on the same, and on click, remove the Dynamic ActiveX ComboBox and place the item on the target cell..

However.. in Weekdays.. I will not able to work on this.. Thread is open for suggestion / improvement..
If you will not got any answer than, I will look into this on weekend..

[YasserKhalil]

Thanks for the idea..
I will wait for your idea .. till the end of the month not only the week..
I'm so patient to reach my goal

[jaslake]

Hi YasserKhalil

Well, I've been playing with this for too long. It's your turn.

As I understand it, you wish to marry the Formulas in Search Combo to Sample Cells A1 to A10.

I have no idea how you'll apply this in real life. See if the Code in the attached does as you require.

[YasserKhalil]

I really don't understand .. how can I use the file .. In range("A1:A10") there is no validation list

[jaslake]

Hi YasserKhalil

In your Post #1 you stated this

I need the validation list to be flexible when typing any part of the text . To enable the auto complete feature ..
....
What I want: to be able to type any part of the text and to find quickly the option from the list

Did you try that with the Sample Workbook? Type any part of the Text...the Code creates the List...try it, see what happens.

Edit: For example...type ap in A1.

[xladept]

Hi John,

I downloaded your sample sheet and typed ap in A1 and nothing happened?

[davesexcel]

I opened John's sample=>cleared column A, then Double Clicked in the specific range, typed in letters and it autofilled.

[xladept]

Hi Dave,

I followed your directions and nothing happened

[jaslake]

Hi XLAdept

Type ap in Sample Sheet A1 then hit the TAB Key (Enter Key is squirrely)

It'll bring up this
1.jpg

Click on the Drop Down Arrow...it'll bring up this
2.jpg
Select an Item from the list then Tab or Enter out.

[jaslake]

Hi Dave

One does not need to double click...the Code does that. See my Post #26.

Thanks for the Feedback. This whole thread is a bit mind bending.

[xladept]

Hi John,

The dropdown didn't manifest

[jaslake]

Hi XLAdept

This is what I do:

Either Download or simply Open the File Sample Auto Complete (version 3.3).xlsm

Enable Macros (of course)

Click on Sample Sheet Cell A1; type in ap

TAB out of the Cell

You'll be presented with a ComboBox with a Dropdown Arrow; click on the Arrow

Select an Item, press the TAB Key or Enter Key

The Cell assumes the Value of the Selected Item.

This is how it performs on MY platform...please don't tell me I'm nuts...it's been a bit of a struggle to get from there to here.

[xladept]

Hi John,

Still no drop down - maybe I'm nuts - I even restarted Excel

[jaslake]

Hi XLAdept

No clue...we'll see if the OP can make it work. I've spent entirely too much time on this Thread. If the OP can't make it work I'm at a loss as to what to do next.

[jaslake]

Hi XLAdept

If you'll humor me, I'd appreciate if you'd try this File. The Code messes with the Enter Key. I've modified the WorkBook Open and Workbook Before Close Code such that the Mods are reset.

Thanks.

[xladept]

@ Jaslake -

I've logged off of my machine that has the 2010 on it - so, Hasta Manana

[YasserKhalil]

It's the same problem as Mr. xladept
Hope it works .. I tried all your instructions in previous posts but no results ..

[karedog]

Hi Yasser,

This is my try in attached file, please look at it.

Regards

[YasserKhalil]

Wonderful Mr. karedog
very fantastic ...
Thanks a lot for this gift
Just a little point how can I clearcontents of any of these cells?

[karedog]

You are welcome.

If the range to cleared is a single cell, just press escape key and press del key.
If more than one cell, the Excel Design Mode must be turned on first, clear the range, and then re-turn off the Design Mode, or you can write a very simple macro to do this like this one :

Please Login or Register  to view this content.

Regards

[YasserKhalil]

Thanks for every bit of information I learned from you

[karedog]

You are welcome, it's my pleasure to help such a nice and friendly person like you.

Regards