Search and retrieve information from columns in Excel into list view

  • Hi I am fairly new to vba so bare with me.

    I currently have a button on a userform which looks at what a user has typed in a text box and checks a chosen column for that word.

    However at the moment the user has to write exactly the same as what is written in the cell for it to recognise it.
    I would like to be able to write part of a word into the search box and it bring up all records that contain that word into a list box.

    my current code


    Any help is thoroughly appreciated

    Thanks
    L

  • Re: Search and retrieve information from columns in Excel into list view


    I think this should do what you want - I have the code below for a form but I also have attached a macro enabled workbook so you can see how it works. Let me know if it needs to be altered or needs other functionality. It has a text box to enter your search term, a listbox for results and 2 buttons (search button and a cancel button) - open up the VBA editor and have a look at the code behind the form. There is some code in Workbook to load the form when the spreadsheet opens. I have some data in the sheet as well so you can try it out.



    There is some more code for the cancel button and some code to load the form on opening the spreadsheet.
    Let me know if you need it modified. Hope it helps
    Anthony

    Files

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Search and retrieve information from columns in Excel into list view


    Please do not revise titles with [SOLVED]. This interferes with searches on the Excel Solver function.
    Title revised to removed the verbage.

  • Re: Search and retrieve information from columns in Excel into list view


    Hi Smuzoen!
    Your example helped me so much an mine now works great!!

    However i cannot open hyperlinks from the data i have searched in the list view.

    Could the same be done with a list box instead of a list view?
    I know opening hyperlinks works in a list box.

    Thanks
    L

  • Re: Search and retrieve information from columns in Excel into list view


    I am not sure what you mean by a List View - this form contains a control called a Listbox and you can navigate to website from the Listbox control.
    You need to add a sub to fire when you click on a result in the Listbox. Try to think of it like this - The Listbox is an object and you need to add code to give instructions what to do with this object when certain events happen to that object e.g. click .
    Add this code to the form

    Code
    Private Sub lbResults_Click()
    Dim navigatetoURL as variant
    navigatetoURL = lbResults.Value
    ActiveWorkbook.FollowHyperlink Address:=navigatetoURL, NewWindow:=True
    
    
    End Sub


    This code will work if the cell contains e.g. http://www.computer.com. What type of hyperlinks are you talking about - to websites? to cell references? workbooks? - the code I have included works for wesbites
    Let me know if this helps
    Anthony

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Search and retrieve information from columns in Excel into list view


    A list view is very similar to a list box.

    But that code helped me alot !
    My code is functioning as it should now :D

    Thanks again for your help!!

    L

  • Re: Search and retrieve information from columns in Excel into list view


    Quote

    Could the same be done with a list box instead of a list view?


    As my code was using a listbox already and not a listview control I misunderstood you - listview is not a standard control in the toolbox - usually has to be added as additional control
    Anyway glad its sorted.
    Anthony

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




Participate now!

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