Lookup using a list box

[lucas]

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

[Jacob Hilderbrand]

For userform1 you can add some code like this:



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


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).

[lucas]

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.


Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm2.Show
End Sub


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

[Anne Troy]

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!

[lucas]

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.

[Jacob Hilderbrand]

Lets say you have the track info in Column H as you indicte with this code:



Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox Worksheets("SalesCatalog").Range("H1")
End Sub


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



Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox Worksheets("SalesCatalog").Range("H" & lstSelection.ListIndex + 1)
End Sub

[lucas]

Jacob,
I removed the line:


ActiveCell.Offset(0, 3) = Worksheets("SalesCatalog").Range("H1")

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

I tried your code:


Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox Worksheets("SalesCatalog").Range("H" & lstSelection.ListIndex + 1)
End Sub


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.



Private Sub lstSelection_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox Worksheets("SalesCatalog").Range("H1")
End Sub


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.

[lucas]

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.



Private Sub lstSelection_Click()
Range("SelectionLink") = lstSelection.ListIndex + 1
End Sub


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

[Jacob Hilderbrand]

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.

[Jacob Hilderbrand]

See attachment for some changes.

[lucas]

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

[Jacob Hilderbrand]

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:



=IF(INDEX(D2343,$E$3,0)=0,"No Data",INDEX(D2343,$E$3,0))

[lucas]

Jacob,
I also did away with the "add selected album" button and used doubleclick instead. seems a little easier to use.