Lots of combo-boxes with key-value pairs

Question

In sheet2 I have data key-value. For example:

1 Thomas
2 Michael

In sheet1 I would have combo-box with items (keys). And if I choose from combo-box for example key 1 then next to combo-box (in cell) I would see value Thomas. It is quite easy with VBA, but I need a lot of such combo-boxes. So if I use VBA then for every combo-box I have to copy the same code of VBA.

Can we do it without VBA? Can we use any formulas? Do you have any idea? If not, how can I make one of code in VBA to all combo-boxes?

Answer 1

Yes, look at Data Validation for your combo boxes for keys and VLOOKUP for values. No VBA needed.

Answer 2

if you want to use VBA, i would recommend something like this:

Sub ComboBox1_Change()
     getName(ComboBox1.Value, "A3")
End Sub

Sub ComboBox2_Change()
     getName(ComboBox2.Value, "A7")
End Sub

Sub getName(key as Integer, destCell as String)
    Sheet1.Range(destCell).Value = Sheet2.Cells(key, 2).Value
End sub

There is a more complicated solution using WithEvents...

I would go with the idea of @Robert Co