Have Excel VBA Find A Value Based On A UserForm Entry

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]