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 ONE13659

[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