[SOLVED] How to create a dynamic search bar that propose result

How to create a dynamic search bar that propose result

I'm trying to create a search bar that autocomplete the word typed in a TextBox by the user, as he's typing. My autocompletion is based on the data written in a column.

The problem is: each cell of the column contains many word (they are key words, each one separated by a comma). My code autocompletes with the whole cell where the word appears not just the word typed.

Another problem is: each cell contains different combination of word but many word are repeated in different cell.

I don't know how can I achieve to autocomplete only one word not a whole cell, that's my main difficulty.

I tried to filter the word and detect the word repeated through the different cells but it didn't worked since each word is in a different word combination. Excel detect the whole combination not just one word once again.

Here is an example of the code I've written - it's functional but it autocompletes the whole cell instead of just one word

'Date
Private Sub TextBoxDT_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim lastRow As Long
    Dim searchRange As Range
    Dim searchString As String
    Dim foundCell As Range

    Set searchRange = ThisWorkbook.Worksheets("Base de données").ListObjects("RCA").ListColumns("Date constatation NC").DataBodyRange
    searchString = TextBoxDT.Text

  
    If searchString = "" Then Exit Sub


    lastRow = searchRange.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

   
    Set foundCell = searchRange.Find(What:=searchString, LookIn:=xlValues, LookAt:=xlWhole)
    If Not foundCell Is Nothing Then
        
        TextBoxDT.Text = foundCell.Value
        TextBoxDT.SelStart = Len(searchString)
        TextBoxDT.SelLength = Len(foundCell.Value) - Len(searchString)
    Else
       
        Set foundCell = searchRange.Find(What:=searchString & "*", LookIn:=xlValues, LookAt:=xlWhole)
    
        
    If Not foundCell Is Nothing Then
          
            TextBoxDT.Text = foundCell.Value
            TextBoxDT.SelStart = Len(searchString)
            TextBoxDT.SelLength = Len(foundCell.Value) - Len(searchString)
    End If
    End If
End Sub

Solution

If your cells hold more than one word (separated by some form of delimiter), you can just use the Function Split(). This function will return an array with all Sub Strings that are the result of splitting the original string at the position of the delimiter you specified.

See the documentation (https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function) for more information.

With that function you should be able to actually get the words contained in the cells not just the whole cell.