Help with Textbox code

[Gregm66]

Hi everyone,

Ok i sorted out my combobox issues whoo hoo,, Special thanks to My Answer Is and Fluff great job guys.

My next question/dilema is, now i am populating several textboxs based on my combo selection. sofar this is working but only returning matching values from one worksheet. My comboboxes look at 2 different worksheets so i can get all my Movie information into my textboxs but when i switch to looking at my Music selections i cant get my textboxes to populate with that corresponding info, see below for a line of code that i am currently using to populate text box.
can i just simply modify this code to allow for 2 worksheets?

i have tried a few different ways of doing this but with no success so far.

TxtCountry.Text = Application.VLookup(cboName.Value, Worksheets("MovieList&Details").Range("B3:R500"), 14, False)

Thanks in advance

 

[Yongle]

Are you saying that the match for cboName.Value could be found on either "MovieList&Details" or "MusicList&Details"
and that if the match is not found on the first sheet it should look on the second

Dim t As String, Movies As Range, Music As Range
    Set Music = Sheets("MusicList&Details").Range("B3", Sheets("MusicList&Details").Range("B" & Rows.Count).End(xlUp)).Resize(, 17)
    Set Movies = Sheets("MovieList&Details").Range("B3", Sheets("MovieList&Details").Range("B" & Rows.Count).End(xlUp)).Resize(, 17)

    On Error Resume Next
    t = Application.VLookup(cboName.Value, Movies, 14, False)
    If t = "" Then t = Application.VLookup(cboName.Value, Music, 14, False)

    TxtCountry.Text = t

Is there a risk of duplicate names between the sheets? (Movies name will be found first and returned)
The easiest way to deal with that would be to modify duplicate names in the second sheet (also affects how cboName should be populated)
eg The Cat and The Cat (mus)
If you do not want to do that let me know - there are several other ways

 

[Gregm66]

thankyou for your reply Yongle,
yes cboName needs to look at 2 diferent worksheets.
and no there is no possible way names can be duplicated.

cheers
i will try your code and see how i go.

 

[Fluff]

Is it just the one textbox you want to fill from the sheet, or more than one?

 

[Gregm66]

Its more that 1 Fluff heres 12 in all, but thanks to Yongle he pointed me in the right direction, ive got them all working now.
again thanks to everyone for their help.

cheers
GregM66