{"id":472,"date":"2019-02-09T03:51:23","date_gmt":"2019-02-09T03:51:23","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=472"},"modified":"2019-02-27T00:18:39","modified_gmt":"2019-02-27T00:18:39","slug":"have-excel-vba-find-a-value-based-on-a-userform-entry","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2019\/02\/09\/have-excel-vba-find-a-value-based-on-a-userform-entry\/","title":{"rendered":"Have Excel VBA Find A Value Based On A UserForm Entry"},"content":{"rendered":"

This post will show you how to find and filter your worksheet based
\non the value in your text box.<\/p>\n

Here is a short description of what we want to do:<\/p>\n

\"\"<\/a><\/p>\n

Since we are using VBA, we can use ADO, and not the standard Find\/Replace function
\nExcel provides.<\/p>\n

The following example will allow the user to enter a name in the text box on the UserForm, and
\nfind the value from the list of contacts on the worksheet. <\/p>\n

Our select statement will cause VBA to seek out the desired row values.<\/p>\n

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.
\nOnly 4 are shown here because of the space issue, but 6 records are found according to the red label.<\/p>\n

\"\"<\/a><\/p>\n

Here is the UserForm:<\/p>\n

\"\"<\/a><\/p>\n

So basically here is the entire code:<\/p>\n

First you click the “Search” button<\/em><\/p>\n

Private Sub btnSearch_Click()\r\n    \r\n    FindContacts\r\nEnd Sub\r\n\r\nSub FindContacts()\r\n\r\n    'Purpose: Load combo with selected contacts\r\n    \r\n    Dim cnn As Object\r\n    Dim rst As Object\r\n    Dim strSQL As String\r\n    Dim lngCount As Long\r\n    Dim intCounter As Integer\r\n    Dim strSearchText As String\r\n    \r\n    Me.cboResults.Clear\r\n    \r\n    'Set up the connection to the Excel worksheet\r\n    Set cnn = CreateObject(\"ADODB.Connection\")\r\n    With cnn\r\n        .Provider = \"Microsoft.ACE.OLEDB.12.0\"\r\n        .ConnectionString = \"Data Source=\" & ThisWorkbook.Path & \"\\\" & ThisWorkbook.Name & \";\" & _\r\n            \"Extended Properties=\"\"Excel 12.0 Xml;HDR=YES\"\";\"\r\n        .Open\r\n    End With\r\n        \r\n    'In order to do queries with a WHERE clause, you need to name the range, otherwise use the worksheet name.\r\n\r\n    strSQL = \"SELECT * FROM [Contacts] WHERE City = '\" & Me.txtSearchText & \"' ORDER BY 'Contact Name'\"\r\n    \r\n    Set rst = cnn.Execute(strSQL)\r\n    \r\n    lngCount = 0\r\n    \r\n    If Not rst.EOF Then\r\n        \r\n        Do Until rst.EOF\r\n            Me.cboResults.AddItem rst(0)\r\n               \r\n           lngCount = lngCount + 1\r\n           rst.Movenext\r\n        Loop\r\n        \r\n        \r\n        rst.Close\r\n        Set rst = Nothing\r\n        cnn.Close\r\n        Set cnn = Nothing\r\n    \r\n        Me.lblMessage.Caption = lngCount & \" record(s) found based on your selection.\"\r\n        DoEvents\r\n    Else\r\n                \r\n        Me.lblMessage.Caption = \"No data found based on your selection.\"\r\n        DoEvents\r\n    End If\r\n\r\n    \r\nEnd Sub\r\n<\/pre>\n
The code filters the list by the entry and add the items to the combo box.<\/p>\n
Watch how it’s done:<\/p>\n