Consulting

Results 1 to 13 of 13

Thread: Solved: Lookup using a list box

  1. #1
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location

    Solved: Lookup using a list box

    I downloaded the base file for this project from http://lacher.com/
    In the original file, you could only add data from the listbox to the active cell. I did some reading and changed the code to add the data to the next empty line. I also added a help file, a sort when you click print(which for testing only goes to print preview), a custom header and a few other knick knacks.

    I would like to emphasize that I did not write this code, I only implemented it. I have been using excel for years but am not a coder.

    My question is:
    I would like to be able to double click on an item in the listbox and have it open a window showing the tracks for that selection. I have tried several things but have had no luck.

    I have attached the file that is working without any of my attempts on it so you can see what I mean. I also do not have the sheet with the tracks in this file but I have been using track1, track2, etc. in a list to try it. There are 2 hidden sheets on the workbook.
    I am using this home project to hone my vba skills but I was hoping someone could give me a lead

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    For userform1 you can add some code like this:

    [vba]
    Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "You double clicked me. You want to get information on the album " & lstSelection.Text
    End Sub
    [/vba]

    This will run when you double click on the listbox. Depending on how you will store the track names you can find them using the listindex properly of the listbox (since that will correspond in some way to the row the data is in).

  3. #3
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location

    need a little more info

    Jacob,
    I used the doubleclick code and was trying to use a list box. Not sure how to connect the list box to the data to the track listing.

    [VBA]Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm2.Show
    End Sub[/VBA]


    Sorry Dreamboat, I didn't take the time to look at the tags. I see it though and thanks.

  4. #4
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Lucas: I fixed your code. If you like it to look that way, just use the VBA tags as directed in the link in my signature.

    Welcome to VBAX!
    ~Anne Troy

  5. #5
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location

    still working

    Well after re-reading Jacob's post, I went back and figured out a few things but I still have one problem to iron out. It all works ok now but I have to figure out in the first listbox properties how to keep it from returning the track listing on the main sheet when you select and insert it to the page. I will figure it out tommorrow, if you have another idea to throw my way jacob, I would appreciate you taking another look at the file, I made a couple of changes to the sheet with the data on it. but it works like you said if I can iron out the wrinkles.

    Thanks for your help.

  6. #6
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Lets say you have the track info in Column H as you indicte with this code:

    [vba]
    Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    MsgBox Worksheets("SalesCatalog").Range("H1")

    End Sub
    [/vba]

    So Row 1 corresponds to listbox listindex 0 (since the listindex starts at 0)
    So then Row 2 should have the data for the second choice from the listbox etc.

    change the code a bit to make the row a variable

    [vba]
    Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    MsgBox Worksheets("SalesCatalog").Range("H" & lstSelection.ListIndex + 1)

    End Sub
    [/vba]

  7. #7
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location

    stumped

    Jacob,
    I removed the line:
    [VBA]
    ActiveCell.Offset(0, 3) = Worksheets("SalesCatalog").Range("H1")

    [/VBA]

    from the first listbox and that solved the problem of the script returning the track listing to the main page.

    I tried your code:
    [VBA]
    Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    MsgBox Worksheets("SalesCatalog").Range("H" & lstSelection.ListIndex + 1)

    End Sub
    [/VBA]

    but I kinda run in to the same problem I am having with the code I'm using for double click, which is that you have to add the selection to the main page before you get a correct reading of the tracks. It is all linked to the selectionlink named range on the salescatalog sheet which is in cell E3. The code I was trying works if you add the album to the sheet and then double click on it in the main listbox. If you double click on any other artist, it just feeds the info to you that is in the selection link at the time.

    this doubleclick code is working if you first add it to the main page and then double click on it in the listbox.

    [VBA]
    Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    MsgBox Worksheets("SalesCatalog").Range("H1")

    End Sub
    [/VBA]

    I appreciate your help, but I may just have to figure out a way to remove the last entry after looking at the tracks for that album.

  8. #8
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location

    I think I got it...

    After thinking about the problem, I finally realized that I needed the listbox to make the selection for me on click.....

    added this code and it works.

    [VBA]
    Private Sub lstSelection_Click()
    Range("SelectionLink") = lstSelection.ListIndex + 1
    End Sub
    [/VBA]

    attached working ver. only first 9 artists-albums have tracks added, the rest return a 0 until I get the rest of the data added...

    thanks for your feedback Jacob

  9. #9
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    You're Welcome

    If you run into any more problems post back. Also since you are now doing this on the click event instead of double click I have two more suggestions.

    1) Use the change event instead of the click event.
    2) Display the information on the userform in another listbox or even on a label since msgbox must be dismissed by the user. I think it would get pretty annoying to have the popup everytime you clicked in the listbox.

  10. #10
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    See attachment for some changes.

  11. #11
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location

    re: your suggestions

    Jacob,
    I like the look of the tracks showing in the textbox. I will probably be able to use both ideas at work now that I understand them better.

    Thanks for your help once again. I got the textbox working. Its attached if you want to take one more look at the end result(still have some tracks to add).
    Steve

  12. #12
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Looks good. If you don't want to have a value of 0 when there is no data you can change your formula in H1 to:

    [vba]
    =IF(INDEX(D2343,$E$3,0)=0,"No Data",INDEX(D2343,$E$3,0))
    [/vba]

  13. #13
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location

    few more small changes

    Jacob,
    I also did away with the "add selected album" button and used doubleclick instead. seems a little easier to use.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •