{"id":230,"date":"2018-11-20T23:28:53","date_gmt":"2018-11-20T23:28:53","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=230"},"modified":"2019-02-17T21:10:43","modified_gmt":"2019-02-17T21:10:43","slug":"how-to-make-a-basic-excel-vba-userform-search-box","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2018\/11\/20\/how-to-make-a-basic-excel-vba-userform-search-box\/","title":{"rendered":"How To Make A Basic Excel VBA UserForm Search Box"},"content":{"rendered":"

In this example I am going to show you how to query your worksheet for particular information using a UserForm. <\/p>\n

Basically we are going to have a worksheet with data in it, and ask it a simple question:<\/p>\n

“Does the criteria I selected in the UserForm exist in the worksheet?”<\/p><\/blockquote>\n

Here is a simple image of the worksheet:<\/p>\n

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

…and here is the simple UserForm which is shown whenever the “Search” button is clicked:<\/p>\n

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

When the UserForm “intializes” the comboboxes are filled in:<\/p>\n

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

Here is the code I am using:<\/p>\n

Private Sub UserForm_Initialize()\r\n    LoadBoxes\r\nEnd Sub\r\n\r\n\r\nSub LoadBoxes()\r\n    Dim intCounter As Integer\r\n    \r\n    With Me.cboYear\r\n        .Clear\r\n        For intCounter = 1 To 70\r\n            .AddItem Sheets(\"Sheet1\").Cells(intCounter, 1).Value\r\n        Next intCounter\r\n    End With\r\n\r\n    With Me.cboMake\r\n        .Clear\r\n        For intCounter = 1 To 70\r\n            .AddItem Sheets(\"Sheet1\").Cells(intCounter, 2).Value\r\n        Next intCounter\r\n    End With\r\n    \r\n    With Me.cboModel\r\n        .Clear\r\n        For intCounter = 1 To 70\r\n            .AddItem Sheets(\"Sheet1\").Cells(intCounter, 3).Value\r\n        Next intCounter\r\n    End With\r\nEnd Sub<\/pre>\n
Since I am using the “Load” feature in multiple places, I am putting all the code in the “LoadBoxes” procedure.<\/strong><\/p>\n
After making my selections, I click on the “Search” button and a SQL statement is executed and a records found count is taken.<\/p>\n
\"Excel_VBA_UserForm_Search_Box_4\"<\/a><\/p>\n
Here is the SQL statement I am using:<\/p>\n
Private Sub btnSearch_Click()\r\n    'Purpose: Find the item on the screen based on the textbox selections\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    \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    \r\n    'In order to do queries with a WHERE clause, you need to name the range, otherwise use the worksheet name.\r\n    'strSQL = \"SELECT * FROM [Sheet1$]\"\r\n    strSQL = \"SELECT * FROM [newtable] WHERE [Year] =\" & Me.cboYear & \" AND [Make] = '\" & Me.cboMake & \"' AND [Model] = '\" & Me.cboModel & \"'\"\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        Do Until rst.EOF\r\n          \r\n           lngCount = lngCount + 1\r\n           rst.Movenext\r\n        Loop\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 selections.\"\r\n        DoEvents\r\n    Else\r\n    \r\n        Me.lblMessage.Caption = \"No data found based on your selections.\"\r\n        DoEvents\r\n    End If\r\n\r\n    \r\nEnd Sub<\/pre>\n
Excel views data that is formatted in consecutive columns as a table.   So if I want to select the data from the entire sheet I can just use “[Sheet1$]”<\/p>\n
However, since I want the ability to query individual columns, I need to set up a “named range”<\/strong>.<\/p>\n
So here is the code all together:<\/p>\n
Private Sub btnReset_Click()\r\n    On Error Resume Next\r\n    Selection.AutoFilter\r\n    \r\n    Me.cboYear.Clear\r\n    Me.cboMake.Clear\r\n    Me.cboModel.Clear\r\n    \r\n    Me.lblMessage.Caption = \"Ready\"\r\n     \r\n    LoadBoxes\r\n    \r\nEnd Sub\r\n\r\nPrivate Sub UserForm_Initialize()\r\n    LoadBoxes\r\nEnd Sub\r\n\r\n\r\nSub LoadBoxes()\r\n    Dim intCounter As Integer\r\n    \r\n    With Me.cboYear\r\n        .Clear\r\n        For intCounter = 1 To 70\r\n            .AddItem Sheets(\"Sheet1\").Cells(intCounter, 1).Value\r\n        Next intCounter\r\n    End With\r\n\r\n    With Me.cboMake\r\n        .Clear\r\n        For intCounter = 1 To 70\r\n            .AddItem Sheets(\"Sheet1\").Cells(intCounter, 2).Value\r\n        Next intCounter\r\n    End With\r\n    \r\n    With Me.cboModel\r\n        .Clear\r\n        For intCounter = 1 To 70\r\n            .AddItem Sheets(\"Sheet1\").Cells(intCounter, 3).Value\r\n        Next intCounter\r\n    End With\r\nEnd Sub\r\nPrivate Sub btnSearch_Click()\r\n    'Purpose: Find the item on the screen based on the textbox selections\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    \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    \r\n    'In order to do queries with a WHERE clause, you need to name the range, otherwise use the worksheet name.\r\n    'strSQL = \"SELECT * FROM [Sheet1$]\"\r\n    strSQL = \"SELECT * FROM [newtable] WHERE [Year] =\" & Me.cboYear & \" AND [Make] = '\" & Me.cboMake & \"' AND [Model] = '\" & Me.cboModel & \"'\"\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        Do Until rst.EOF\r\n           'output = output & rst(0) & \";\" & rst(1) & \";\" & rst(2) & vbNewLine\r\n           'Debug.Print rst(0); \";\" & rst(1) & \";\" & rst(2)\r\n           \r\n           lngCount = lngCount + 1\r\n           rst.Movenext\r\n        Loop\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 selections.\"\r\n        DoEvents\r\n    Else\r\n    \r\n        Me.lblMessage.Caption = \"No data found based on your selections.\"\r\n        DoEvents\r\n    End If\r\n\r\n    \r\nEnd Sub<\/pre>\n
Let me know if you have any questions.<\/p>\n
[simple_contact_form]\t<\/p>\n
****************************************************<\/p>\n\n\n
\n
<\/div>\n