Code_Goes_Here
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Predictive text inside data validation row
0
Answers
0
Selected Answer
Excel will autocomplete any cell in the way you describe based on previous content in the same column, even if the entry was made by selection from a validation dropdown. So, if you have a column with Yes/No validations, "Y" and "N" will expand into "Yes" and "No" after both values were selected in the same column at least once before.
Note that this is tricky at times (Excel 2010). If your list has "Yes", entering lower case "y" will autocomplete into "yes", not "Yes".
The alternative is an ActiveX combo box which does the autocomplete 100% reliably and case sensitive, meaning, entering "y" will result in the "Yes" which is in the list, depending upon the setting of the MatchEntry property. I have always balked at the prospect of having dozens, hundreds or even thousands of combo boxes in a worksheet, however. Therefore I used VBA code to create the combo box on the fly when the cell is clicked and delete it when the cell loses the focus, leaving behind the selected value.
Edited 29 Dec 2018 ======================
The flying combo box is demonstrated in the attached workbook. There is code in the code module of the "POLRES" tab which must be in the code module of the sheet on which you want to see the action. There is more code in the module "Utilities" which supports the other code and must be in a standard code module (of any name). Please read the comments I have added to the code.
Discussion
I've attached the sheet titled "POLRES Policies"
Column I is where I need the autocomplete based on the data validation in sheet Roster in column A. Right now I have th data validation working but auto complete in that column would be so nice daily! : )
Once I've seen what you've done I should be able to replicate this. Seems
Column I is where I need the autocomplete based on the data validation in sheet Roster in column A. Right now I have th data validation working but auto complete in that column would be so nice daily! : )
Once I've seen what you've done I should be able to replicate this. Seems
Sroncey21 (rep: 66) Dec 28, '18 at 12:45 pm
I see what you did! It works but when I clear out all the names and try to type any of the names in my list it 1) auto enters "Amy Short" wherever I click and 2) if I type over her name it will auto default back to "Amy short" after I type and press enter : / - I appreciate all your help on this. This is a tough one.
Sroncey21 (rep: 66) Dec 29, '18 at 11:29 am
If you don't want the autofill look for this code in the event procedure.
Delete the two lines: Else and .ListIndex = 0. You will get a blank combobox. You might also keep the Else and replace the second line with .Text = "Select or type". If you prefer this version I would have to make some more changes to the code to remove that line when the user types nothing.
As for the combobox's behaviour when you hit Enter (or Tab), please address you complaint to Mr Gates organisation. That's how ActiveX combo boxes are designed to work. To change their value you need to exit them, and you exit them by clicking elsewhere. There may be a way around that but it doesn't start by marking my answer as wrong. My answer is correct and complete (more than complete, in fact, if you count the list sorting).
If Len(Tmp) Then
.Text = Tmp
Else
.ListIndex = 0
End If
Delete the two lines: Else and .ListIndex = 0. You will get a blank combobox. You might also keep the Else and replace the second line with .Text = "Select or type". If you prefer this version I would have to make some more changes to the code to remove that line when the user types nothing.
As for the combobox's behaviour when you hit Enter (or Tab), please address you complaint to Mr Gates organisation. That's how ActiveX combo boxes are designed to work. To change their value you need to exit them, and you exit them by clicking elsewhere. There may be a way around that but it doesn't start by marking my answer as wrong. My answer is correct and complete (more than complete, in fact, if you count the list sorting).
Variatus (rep: 4889) Dec 29, '18 at 10:05 pm