0

I have a drop down list with some values and I want that when the value entered manually to the cell does not match the exact value of the list (lower and upper cases), the value entered to the cell is the one from the list (matching exactly lower and upper cases).

For example, let's say I have the following list:

One

Two

Three

And then I have a drop down list in cell A1 whose data comes from the previously mentioned list. If I manually enter "one", the cell will validate it and the word "one" will be the new value of that cell. The problem is, that the correct word is "One" (the first letter is a capital letter). So, is there a way where Excel can "correct" the manually entered word and if I write "one" for example, the value of the cell becomes "One"?

Thanks in advance,

N. Pavon
  • 821
  • 4
  • 15
  • 32
  • Possible duplicate of [Auto-Complete with only text and not numbers ComboBox Excel VBA](http://stackoverflow.com/questions/37572688/auto-complete-with-only-text-and-not-numbers-combobox-excel-vba) – Ralph Mar 20 '17 at 16:10
  • Here is another post which might be of interest to you: http://stackoverflow.com/questions/42879261/how-to-filter-listbox-values-based-on-a-textbox-value/42880069#42880069 – Ralph Mar 20 '17 at 16:11
  • Surely that is what the drop down list : data validation does (at least one of its options) - allows the user to select from a list supplied. – Solar Mike Mar 20 '17 at 16:13
  • @SolarMike You are correct. Yet, the matches found in the list must match the first letters you type. So, if you start typing `c` then it will only find entries which start with c such as `corporation`. Hence, this will not find (nor suggest) `Intl. Corporation` or `International Corp.` even when you continue typing `corp`. Therefore, the above referenced solutions are usually preferred. Yet, if you know the first few letters at all times (like a country list where you know how to spell the country like `au` for Australia, `in` for India) then your suggestion is perfectly fine and much easier! – Ralph Mar 20 '17 at 16:36
  • @Ralph I checked both questions and they do not address my issue. I will edit the question and add more details to it. – N. Pavon Mar 20 '17 at 17:05
  • I am assuming that you are using ActiveX controls. If you set `MatchEntry = fmMatchEntryComplete` and have only the correct spellings of the words in the list (starting with a capital letter) then Excel will automatically match the entries (as outlined by @SolarMike) while ignoring upper and lower case (similar to `vbCompareText`). So, `oNE` will be recognized and `One` will be automatically selected. – Ralph Mar 20 '17 at 17:22
  • @Ralph I'm using Data Validation – N. Pavon Mar 20 '17 at 17:30
  • Then you're out of luck (unless you want to implement any of the above referenced solutions or switch over to an ActiveX ListBox). – Ralph Mar 20 '17 at 17:32
  • @Ralph If the list has to be displayed in many cells, I would have to add as many ActiveX ListBox as cells containing the lists? – N. Pavon Mar 20 '17 at 17:35
  • The alternatives would be to (1) correct spellings later (using a macro going accross the entire sheet and validating all entries later) or (2) add `One` to the auto-correct list: https://support.office.com/en-us/article/Choose-AutoCorrect-options-for-capitalization-spelling-and-symbols-e7433b94-f3de-4532-9dc8-b29063a96e1f – Ralph Mar 20 '17 at 17:44

0 Answers0