Macro to retrieve values corresponding to the value selected in Dropdown box

[Marvin]

Hello all,

I have lot of Drop down boxes in a sheet (Sheet1). Let me consider
"Drop Down 227" for explaning what I'm try to accomplish. Input range
for Values in this drop down box are Sheet2!$A$2:$A$200, which are
populated with text (mix of characters and numbers).

Cells B2 to E2 in Sheet 2 are values corresponding to A2 in Sheet2.
Cells B3 to E3 in Sheet 2 are values corresponding to A3 in Sheet2.
Cells B4 to E4 in Sheet 2 are values corresponding to A4 in Sheet2.
............
Cells B200 to E200 in Sheet 2 are values corresponding to A200 in
Sheet2.

When the user picks a value from "Drop Down 227" box which resides in
cell I10 in Sheet 1, I would like cell J10 (in Sheet1) to
automatically populate the entries (from Sheet 2) corresponding to
it.
For example, assuming that user has picked the second entry from the
"Drop down 227", then it corresponds to cell A3 in sheet2. So, cell
J10 in Sheet1 should have the values of cells B2 thru E2 from sheet 2,
each seperated by comma.

Hope I've made it clear.

Any help would be much appreciated. If I know how to do this, then I'm
quite sure that I can extend this principle to all drop down boxes in
my worksheet.

Thanks.

 

[Pete_UK]

You've posted this in a programming group, but you can accomplish what
you want with a formula - put this in J10:

=VLOOKUP(I10,Sheet2!A$2:E$200,2,0)&", "&VLOOKUP(I10,Sheet2!A$2:E
$200,3,0)&", "&VLOOKUP(I10,Sheet2!A$2:E$200,4,0)&",
"&VLOOKUP(I10,Sheet2!A$2:E$200,5,0)

Hope this helps.

Pete

 

[Marvin]

Pete,

Excellent! I manually entered the value in I10 and this works. But I
don't have the value directly in cell I10. Instead, I have a drop bown
box (Drop Down 227) in I10. So, how do I equate the value of cell I10
to be equal to the value selected in the 'Drop Down 227' box? Please
let me know! It should solve everything then... Thanks very much.

 

[Marvin]

Ok! I think I got it.

Sub DropDown227_Change()
With ActiveSheet.Shapes("Drop Down 227").ControlFormat

Range("I10").Value = .List(.Value)
End With
End Sub

If there is any other way to accomplish the same in a better way, I'd
be keen to know. Since the sheet I have has lot of drop down boxes,
now I have to copy paste and modify the code for 230 drop down boxes!
Anyway to simplify this?

 

[Pete_UK]

If I want to set up a drop-down box in a cell, linked to a list of
values elsewhere, I would tend to use Data Validation. But then, I
don't usually need to set up 200+ of them !! <bg>

Debra Dalgleish has some excellent tutorials on DV which can be found
here:

http://www.contextures.com/xlDataVal01.html

Hope this helps.

Pete