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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Private mbEventsDisabled As Boolean Private Sub TextBox1_Change() Dim vaRecent As Variant Dim i As Long Dim sEntered As String If Not mbEventsDisabled Then sEntered = Me.TextBox1.Text If Len(sEntered) <= 5 Then vaRecent = Me.ListBox1.List For i = LBound(vaRecent, 1) To UBound(vaRecent, 1) If Left$(vaRecent(i, 0), Len(sEntered)) = sEntered Then mbEventsDisabled = True With Me.TextBox1 .Text = vaRecent(i, 0) .SelStart = Len(sEntered) .SelLength = Len(vaRecent(i, 0)) End With mbEventsDisabled = False Exit For End If Next i End If End If End Sub |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
Private bBackSpace As Boolean Private mbEventsDisabled As Boolean Private Sub TextBox1_Change() Dim vaRecent As Variant Dim i As Long Dim sEntered As String If Not mbEventsDisabled Then sEntered = Me.TextBox1.Text If bBackSpace And Len(sEntered) > 0 Then sEntered = Left$(sEntered, Len(sEntered) - 1) If Len(sEntered) < 5 Then vaRecent = Me.ListBox1.List For i = LBound(vaRecent, 1) To UBound(vaRecent, 1) If Left$(vaRecent(i, 0), Len(sEntered)) = sEntered Then mbEventsDisabled = True With Me.TextBox1 .Text = vaRecent(i, 0) .SelStart = Len(sEntered) .SelLength = Len(vaRecent(i, 0)) End With mbEventsDisabled = False Exit For End If Next i End If End If End Sub Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) bBackSpace = KeyCode = 8 End Sub |
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.
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.
I agree with Andy.
But this will suffice:
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.