Autocompletion in a TextBox - VBA

I am trying to do a function to do some autocompletion in a TextBox. I want to autocomplete word per word but currently the autocompletion is only effective on the first typed word.
For exemple if i write "ba" it autocompletes easely with "banana" from the column wanted no problem on this point. But when I write another word after "banana" (with one space between them two obviously) the autocomplete is not working. It only work on one word

Can you help me ? Here is new code :

Private Sub TextBoxComm_Change()
Dim lastRow As Long
Dim searchRange As Range
Dim foundCell As Range
Dim chaine, Mot_Proposé As String
Dim word As String 'WORD FAIT OFFICE DE SEArch string

 If m_ignore Then Exit Sub

'Définition du champs de recherche
word = TextBoxComm.text
Set searchRange = ThisWorkbook.Worksheets("Base de données").ListObjects("RCA").ListColumns("Numéro de commande").DataBodyRange

'si c'est vide ne rien faire
If word = "" Then Exit Sub

'Trouve la dernière cellule de la colonne
lastRow = searchRange.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

' Si le mot tapé n'apparaît trouver le mot le plus ressemblant
Set foundCell = searchRange.Find(What:=word & "*", LookIn:=xlValues, LookAt:=xlWhole)

If Not foundCell Is Nothing Then
        'Compléter le mot tapé
        chaine = foundCell.Value
        m_currentSuggestion = Find_Mot(word, chaine)
        TextBoxComm.text = m_currentSuggestion
        
    If Len(m_currentSuggestion) > 0 Then
        m_currentText = m_currentSuggestion
        m_selectionStart = Len(word)
        m_selectionLength = Len(m_currentSuggestion) - Len(word)
        
        TextBoxComm.text = m_currentText
        TextBoxComm.SelStart = m_selectionStart
        TextBoxComm.SelLength = m_selectionLength
        
    End If
    End If
    
   End Sub

And my first version was this one : (just on another TextBox but i apply the same code on every textbox)

'Machine
Private Sub TextBoxMach_Change()
Dim lastRow As Long
Dim searchRange As Range
Dim searchString As String
Dim foundCell As Range
Dim chaine, Mot_Proposé As String

 If m_ignore Then Exit Sub

'Définition du champs de recherche
Set searchRange = ThisWorkbook.Worksheets("Base de données").ListObjects("RCA").ListColumns("Type de machine").DataBodyRange
searchString = TextBoxMach.text

'Ne rien faire si le TextBox est vide
If searchString = "" Then Exit Sub

'Trouve la dernière cellule de la colonne
lastRow = searchRange.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Chercher si le mot tapé apparaît déjà dans la colonne
Set foundCell = searchRange.Find(What:=searchString, LookIn:=xlValues, LookAt:=xlWhole)


If Not foundCell Is Nothing Then
    'Si oui, autocomplétion
        
    chaine = foundCell.Value
    Mot_Proposé = Find_Mot(searchString, chaine)
    TextBoxMach.text = Mot_Proposé
    
    TextBoxMach.SelStart = Len(searchString)
    TextBoxMach.SelLength = Len(foundCell.Value) - Len(searchString)
Else
    ' Si le mot tapé n'apparaît trouver le mot le plus ressemblant
    Set foundCell = searchRange.Find(What:=searchString & "*", LookIn:=xlValues, LookAt:=xlWhole)


If Not foundCell Is Nothing Then
        'Compléter le mot tapé
        
        chaine = foundCell.Value
        Mot_Proposé = Find_Mot(searchString, chaine)
        TextBoxMach.text = Mot_Proposé
    
        TextBoxMach.SelStart = Len(searchString)
        TextBoxMach.SelLength = Len(foundCell.Value) - Len(searchString)
End If
End If
End Sub

I was looking for something similar myself today for WPF and bumped into a post on learn.microsoft.com. I translated it from C# to VBA and fixed an issue with the Backspace key that was preventing a letter to be deleted when the key was pressed.

The example uses a hard-coded list of words, but you can modify it to get the data from Excel to fill an array when the form is initialized.

Source: Auto Complete for TextBox in WPF (MVVM)

Private fields and source collection:

Option Explicit

Private m_words As VBA.Collection
Private m_currentInput As String
Private m_currentSuggestion As String
Private m_currentText As String
Private m_selectionStart As Integer
Private m_selectionLength As Integer
Private m_ignore As Boolean

'Initialize source
Private Sub UserForm_Initialize()
    Set m_words = New VBA.Collection
    Populate
End Sub

Private Sub Populate()
    m_words.Add "English"
    m_words.Add "Chinese"
    m_words.Add "French"
    m_words.Add "French one"
    m_words.Add "French two"
    m_words.Add "Russian"
    m_words.Add "Japanese"
End Sub

The Change() event handler:

'Change()
Private Sub AutoTextBox_Change()

    If m_ignore Then Exit Sub
    
    Dim word As String
        word = AutoTextBox.Text
    
    If Len(word) > Len(m_currentInput) And word <> m_currentSuggestion Then
        
        m_currentSuggestion = FindWord(word)
        
        If Len(m_currentSuggestion) > 0 Then
            m_currentText = m_currentSuggestion
            m_selectionStart = Len(word)
            m_selectionLength = Len(m_currentSuggestion) - Len(word)
            
            AutoTextBox.Text = m_currentText
            AutoTextBox.SelStart = m_selectionStart
            AutoTextBox.SelLength = m_selectionLength
        End If
    End If
    
End Sub

A helper method to search for the keyed word, similar to the C# StartsWith(string).

'Find word in source that starts with given param.
Private Function FindWord(ByVal beginsWith As String) As String
    
    Dim i As Integer
    
    For i = 1 To m_words.Count
        If LCase(Left(m_words(i), Len(beginsWith))) = LCase(beginsWith) Then
            FindWord = m_words(i)
            Exit Function
        End If
    Next
    
    FindWord = vbNullString
    
End Function

Using an array, the loop is slightly different:

For i = LBound(array) To UBound(array)
    '...
Next

A hack to suspend the TextBox_Change() event when the Backspace key is pressed:

'Suspend the Change() event to allow the backspace key.
Private Sub AutoTextBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    m_ignore = Len(AutoTextBox.Text) > 0 And KeyCode = 8 'Backspace key
End Sub

Result: