Displaying selection from Listbox on userform

  • Hi

    I have been trying to create a "Search" or "Look Up" form for my database. (Attached file - "Test - Form").

    I have been given a lot of help/ideas from this forum with which I managed to get to the stage where I could select the criteria i wanted to search by using a combobox and textbox in the userform. On hitting the "Find" button it shows all the results in the listbox.

    The trouble started when I tried to display the listbox selection on the labels at the bottom of the userform. As the listbox is small and cant show all the fields properly, I need to display them in labels once user selects a particular record from listbox.

    I managed to find some examples of this from this forum. (file attached "Action Log"). As I am not an Excel/ VBA expert, I have missed something and am not able to make it work.

    Can someone please look at my file "Test - Search Form" and tell me where I have gone wrong. It keeps giving me message saying "Out of range". I am not sure how to define ranges correctly.

    I would appreciate any help!


  • Re: Displaying selection from Listbox on userform

    without looking at your files, the code for this is usually something like

    textbox1 = listbox1.list(listbox1.listindex, 0)

    the final ,0 is not needed if you are only using one column.

    But if you are using say 4 columns,

    listbox1.columncount = 4

    then you can specify the item with

    textbox1 = listbox1.list(listbox1.listindex, 0)
    textbox2 = listbox1.list(listbox1.listindex, 1)
    textbox3 = listbox1.list(listbox1.listindex, 2)
    textbox4 = listbox1.list(listbox1.listindex, 3)

    [COLOR="Purple"]Eternity is a terrible thought. I mean, where's it going to end??[/COLOR]

  • Re: Displaying selection from Listbox on userform

    hi fengore....thanks for your response. I have tried to use following code, but it is giving me error saying : Run time error "9" , "Subscript out of range".

    Here's what I've got:

    I have feeling that I am not specifying the range properly. following is the complete code in the userform:

    Please note:
    The worksheet in which the data is stored is called "ComplaintData" and it will be hidden (will not be the active sheet).

    Your help will be much appreciated!

  • Re: Displaying selection from Listbox on userform

    Hi Dave....sorry for late reply over the weekend I didnt have access to my office comp....

    It has started doing funny things now.....now it has started giving me error
    "1004 - Extract name has a missing or illegal field name" in the following line:

    DataSH.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, criteriarange:=DataSH.Range("L1:L2"), copytorange:=DataSH.Range("N1:U1")

    This part was working fine before....but i think i managed to mess up with the range while trying to get the other part to work....


  • Re: Displaying selection from Listbox on userform

    You probably need to Clear the range before having the filtered dat copied there. That is

    With DataSH
         .Range("A1").CurrentRegion.AdvancedFilter _
          Action:=xlFilterCopy, criteriarange:=.Range("L1:L2"), _
     End With
  • Re: Displaying selection from Listbox on userform

    Hi Dave, I managed to solve problem in my original file where I can get the the userform1 to search & display record lines in the listbox again.

    Now the problem I have is:

    1. If I try to search by a customer thats not in the database (in sheet "ComplaintData", then it gives me error. I would like it to say something like "There no complaint for this Cusdtomer"

    2. When I search by customer that is in the databse, it shows results in the listbox on userform1. As soon as I click on the line in the listbox, it gives me an error. I want to be able to select record in the listbox, as eventually theer will be more than one records for each customer e.g. complaint for same customer but different product or category)

    3. I want to be able to click on the desired line in list box and display the details of that record in the labels below, as the list box doesnot have enough space to show all the fields completely.

    Hope you can help....cheers

  • Re: Displaying selection from Listbox on userform

    Hi...I managed to make it work....I used following code:


  • Re: Displaying selection from Listbox on userform

    I know this is an old thread, but I wanted to thank you, Vikrampnz. You happened to solve one of my problems on a similar note, so I'm in a good mood now.


    Many thanks, :music:

  • Re: Displaying selection from Listbox on userform

    I coultnt open the Properties window Its has been protected with password can u share password

Participate now!

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