This post will show you how to find and filter your worksheet based
on the value in your text box.
Here is a short description of what we want to do:
Since we are using VBA, we can use ADO, and not the standard Find/Replace function
Excel provides.
The following example will allow the user to enter a name in the text box on the UserForm, and
find the value from the list of contacts on the worksheet.
Our select statement will cause VBA to seek out the desired row values.
In the following example, we are looking to populate the combo box with customers in “London”. So “London” gets typed in, and 6 rows get found by the code.
Only 4 are shown here because of the space issue, but 6 records are found according to the red label.
Here is the UserForm:
So basically here is the entire code:
First you click the “Search” button
Private Sub btnSearch_Click() FindContacts End Sub Sub FindContacts() 'Purpose: Load combo with selected contacts Dim cnn As Object Dim rst As Object Dim strSQL As String Dim lngCount As Long Dim intCounter As Integer Dim strSearchText As String Me.cboResults.Clear 'Set up the connection to the Excel worksheet Set cnn = CreateObject("ADODB.Connection") With cnn .Provider = "Microsoft.ACE.OLEDB.12.0" .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" .Open End With 'In order to do queries with a WHERE clause, you need to name the range, otherwise use the worksheet name. strSQL = "SELECT * FROM [Contacts] WHERE City = '" & Me.txtSearchText & "' ORDER BY 'Contact Name'" Set rst = cnn.Execute(strSQL) lngCount = 0 If Not rst.EOF Then Do Until rst.EOF Me.cboResults.AddItem rst(0) lngCount = lngCount + 1 rst.Movenext Loop rst.Close Set rst = Nothing cnn.Close Set cnn = Nothing Me.lblMessage.Caption = lngCount & " record(s) found based on your selection." DoEvents Else Me.lblMessage.Caption = "No data found based on your selection." DoEvents End If End Sub
The code filters the list by the entry and add the items to the combo box.
Watch how it’s done:
Let me know if you have any questions.
Use this form to send me a message:
[simple_contact_form]