Search Listbox item and upon double click selected item appear on SubForm in TxtBoxes

    Hi All,


    I am not sure how to do this, I have spreadsheet attached, which has search function and shows matches based on search column B ( Name). The idea is to also search base on the column C (Other Name) as search criteria?? SO either Column B or Column C should search the same record and show in the list box. Currently, only Column B is coded in the spreadsheet, would need help with adding Col C to gives the same record in listbox??? Then,


    Upon finding the desired item in the listbox for example "John Cona" or "jc", click on the record "John Cona" from the listbox and USERFORM2 gets open and shows the results in the corresponding textboxes already designed in the excelsheet as USERFORM2.


    Would highly appreciate your expertise and help here with the code....


    many thanks in advance.


    Ozz

    Re: Search Listbox item and upon double click selected item appear on SubForm in TxtB


    I think this does what you want -amend relevant line

    Code
    If InStr(1, .List(x, 1), TextBox1.Text, vbTextCompare) = 0 And _
                InStr(1, .List(x, 2), TextBox1.Text, vbTextCompare) = 0 Then
                .RemoveItem (x)

    The first bit at least - are you asking for the second bit too?

    Re: Search Listbox item and upon double click selected item appear on SubForm in TxtB


    awesome, the first bit works like charm :) thanks


    Yes, please for the second bit of code. Thanks in advance!

    Re: Search Listbox item and upon double click selected item appear on SubForm in TxtB


    OK. I put this in the listbox double click event rather than click but you can change if you like

    Re: Search Listbox item and upon double click selected item appear on SubForm in TxtB


    Dude, you are great. thanks so much for your help here....It is working as it should be. :)

    Re: Search Listbox item and upon double click selected item appear on SubForm in TxtB


    StephenR, Is that possible to use Or ( maybe similar function) instead of AND in this code?
    So e.g., "Name" in Column (B) or "Alias Name" Column (D). I should be able to use either column B or D to pull the results? Please help here. Many thanks.


    If InStr(1, .List(x, 1), TextBox1.Text, vbTextCompare) = 0 And _
    InStr(1, .List(x, 2), TextBox1.Text, vbTextCompare) = 0 Then
    . RemoveItem (x)

    Re: Search Listbox item and upon double click selected item appear on SubForm in TxtB


    Hi StephenR,


    I seems to have issue with the code on double clicking on the listbox item and userform2 open with corresponding row data shows in the textboxs from the listbox.


    The issue is when I double click on the listbox item the userform2 opens up but it does not shows data that was double clicked on the listbox userform1. But when I double click it again on the same listbox row data then the data appears on the userform2. So, it takes two times double clicking on the same listbox row to appear on the userform2. I tried but seems like not able to get around the issue....Can you please help here...The sheet is attached. thanks much in advance....


    Ozz

    Re: Search Listbox item and upon double click selected item appear on SubForm in TxtB


    I would prefer to use a button to do the UF2 bit. With the Click event it fires when you don't want it to for reasons which are not immediately apparent to me. You can see here. Btw this does assume there is only one entry in the listbox.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login