The Daily Dose

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.

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.

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.