• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search value in a Textbox and return the result

saamrat

Member
Dear Experts,

When a value entered in Textbox1, it searches the data in a closed worksheet and return the result. If Textbox1 value is product, Textbox2 return the description of product. Let the workbook contains data remain closed. There will be 2 workbooks. Workbook1 will have the userform and there will be another workbook holds all the datas.
Kindly help
 

Attachments

Kenneth Hobson

Active Member
saamrat, as I explained in one of your other threads, I would use a combobox for the pick/search control rather than a textbox. A listbox would work the same. Either control can show several columns. It would be faster that way for some to see related results for a pick.

In anycase, I did replace your textbox1 with combobox1. Add a command button control or delete that part of the code. Change the combobox1.tag value to point to your xlsx file. Change the value of sw to the tab name for your worksheet in that workbook. Add the ADO reference as commented.

Code:
Dim aDat

Private Sub UserForm_Initialize()
    'Tools > References > Microsoft ActiveX Data Objects 2.8 Library
    Dim sw As String, rs As Recordset

    sw = "Data" 'Worksheet tab name
    ComboBox1.Tag = ThisWorkbook.Path & "\Dat_Demo.xlsx"

    Set rs = New Recordset
    With rs
        .Open "SELECT * FROM `" & sw & "$`;", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            ComboBox1.Tag & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
        aDat = rs.GetRows   'Get all the rows/records
        ComboBox1.List = Application.Index(aDat, , 1)
    End With
End Sub

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then TextBox2 = aDat(ComboBox1.ListIndex, 2)
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub
 
Top