Userform Textbox Autocomplete

I’m working on a project where the user types some stuff into a textbox. A good portion of the time, what the user will type will match one of the last few things he typed. I wanted the textbox to autocomplete if there was a match to a list. Pretty simple, I think. For purposes of this demonstration, I’m going to match to a list of random sentences in a listbox.

I had to use that old disable events in a userform trick otherwise setting the .Text property would call the change event again.

I only look at the first five characters. After that, you just have to type what you want. If there’s a match, I set the .Text property to the matching sentence and set the selection so that the user can continue typing. It all worked very nicely except for backspacing. In the above screenshot, I’ve typed He but the textbox contains the whole sentence. If I hit backspace in this situation, I delete the highlighted portion and I’m left with He. Backspace does nothing.

I was hoping to find a simple and elegant solution. Instead, I did this.

I’m using a module-level variable to determine if the backspace was pressed while in the textbox. If it was and there’s still at least one character, I simply shorten the sEntered variable by one character. That leaves the whole SelStart and SelLength mechanism working as expected.

3 thoughts on “Userform Textbox Autocomplete

  1. Hi Dick,

    How about using a combobox instead. The random sentences can be added to the list.
    The combobox will then do all the work for you.

  2. I agree with Andy.
    But this will suffice:

  3. The actual application is a multi-line textbox with word wrap, which comboboxes don’t have. But looking down the list, there hasn’t been an entry longer than one line in a quite a while. It’s probably a good trade-off to switch to a combobox and lose word wrap.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.