LISTBOX : search by first letter

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

Hello.

Code:

---

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

---

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

Quote:

---

Originally Posted by jonh

Hello.

Code:

---

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 ONEAttachment 13659

Quote:

---

Originally Posted by Pongwipat

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

There are a couple of comments in that code.

Quote:

---

Originally Posted by jonh

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

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

Or change this

Code:

---

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

---

to this

Code:

---

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

---

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

try replacing

Code:

---

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

---

with

Code:

---

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

---

Hello everyone
Please there's not a way of out of this problem
cordially
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.

Quote:

---

Originally Posted by 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.

---

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

Quote:

---

Originally Posted by GAVIOTA ONE

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.

Quote:

---

Originally Posted by SamT

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

---

Hello SamT
thank you ..
cordially
GAVIOTA ONE