LISTBOX : search by first letter

[GAVIOTA ONE]

Hello FORUM
Hello everyone,
I really need your help on how to do my research in my listbox1 in USF1 ..once I type the first letter of a word in textbox1 automatically displays all words begin with that letter .. and in TextBox2 appears the number of these words found
my basic data is the sheet (DATABASE).
thank you in advance
cordially
GAVIOTA ONE

[jonh]

Hello.

Private Sub TextBox1_Change()
    
    'requires reference to Microsoft ActiveX Data Objects xxx Library
    
    'https://www.connectionstrings.com/excel/
    Const cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Extended Properties='Excel 12.0 Xml;HDR=NO;';"
    
    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, ws As Worksheet
    Set ws = ThisWorkbook.Sheets("database")
    ListBox1.Clear
    With cn
        .Open cs & "Data Source=" & ws.Parent.FullName
            With rs
                .Open "SELECT [f4] FROM `database$` where [f4] <> 'name' and " & _
                    "[f4] like '%" & TextBox1 & "%'", cn, adOpenStatic, adLockReadOnly
                Do Until .EOF
                    ListBox1.AddItem .Fields(0)
                    .MoveNext
                Loop
                TextBox2 = rs.RecordCount
                .Close
            End With
        .Close
    End With
End Sub

[Pongwipat]

I have one example like your work. It may be useful for you.

[GAVIOTA ONE]

Hello.

Private Sub TextBox1_Change()
    
    'requires reference to Microsoft ActiveX Data Objects xxx Library
    
    'https://www.connectionstrings.com/excel/
    Const cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Extended Properties='Excel 12.0 Xml;HDR=NO;';"
    
    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, ws As Worksheet
    Set ws = ThisWorkbook.Sheets("database")
    ListBox1.Clear
    With cn
        .Open cs & "Data Source=" & ws.Parent.FullName
            With rs
                .Open "SELECT [f4] FROM `database$` where [f4] <> 'name' and " & _
                    "[f4] like '%" & TextBox1 & "%'", cn, adOpenStatic, adLockReadOnly
                Do Until .EOF
                    ListBox1.AddItem .Fields(0)
                    .MoveNext
                Loop
                TextBox2 = rs.RecordCount
                .Close
            End With
        .Close
    End With
End Sub

hello JONH
thank you for help
I do not know where is the problem in this code
please see this picture
cordially
GAVIOTA ONE1.jpg

[GAVIOTA ONE]

I have one example like your work. It may be useful for you.

Hello Pongwipat
thank you for help
it's beautiful example
I still have only the number of the desired words
cordially
GAVIOTA ONE

[jonh]

There are a couple of comments in that code.

[GAVIOTA ONE]

There are a couple of comments in that code.

HIII JONH
thank you very much for help me
I am a beginner in vba excel
Please can you put me the code in this file below
thank you in advance
cordially
GAVIOTA ONE

[jonh]

Either go to the code window and select tools > references and add the library.

Or change this

    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, ws As Worksheet

to this

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

[GAVIOTA ONE]

Hiii jonh
it does not work
thank you for your suggestion
cordially
GAVIOTA ONE

[jonh]

try replacing

    Const cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ 
        "Extended Properties='Excel 12.0 Xml;HDR=NO;';"

with

    Const cs As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Extended Properties="Excel 8.0;HDR=no;IMEX=1";

[GAVIOTA ONE]

Hello everyone
Please there's not a way of out of this problem
cordially
GAVIOTA ONE

[jonh]

Of course there is. But simply saying 'it doesn't work' won't make it work.
If the code errors, tell us what the error says.

[GAVIOTA ONE]

Of course there is. But simply saying 'it doesn't work' won't make it work.
If the code errors, tell us what the error says.

Hello JONH
the file is before you and your code is before you
trying this code and you will see the error
cordially
GAVIOTA ONE

[SamT]

Hello JONH
the file is before you and your code is before you
trying this code and you will see the error
cordially
GAVIOTA ONE

This is not the situation. The computer of Jonh is not the computer of GAVIOTA.

[GAVIOTA ONE]

This is not the situation. The computer of Jonh is not the computer of GAVIOTA.

Hello SamT
thank you ..
cordially
GAVIOTA ONE