Display value linked to Combo Box Selection
 

Simple question, I think.
I have a worksheet with a two column array. First column is 2-letter state
abbreviation, second column is full state name.
In a userform, i want the user to select the 2-letter abbreviation, and I
want to display the corresponding state name in the adjacent box (listbox?
textbox?).

If I can get help with this, I would hope to use the same method to make
other, similar linked selections available on the form.

Help?
Thanks.

Display value linked to Combo Box Selection
 

Assuming the state list is A1:B50, set the combobox rowsource to A1:A50.
Then you can use the following for the _Change event procedure for your
combobox (substitute in the actual names of your controls, of course):

Private Sub ComboBox1_Change()

TextBox1.Value = Range("A1").Offset(ComboBox1.ListIndex, 1)

End Sub

--
- K Dales


"Bill" wrote:

Simple question, I think.
I have a worksheet with a two column array. First column is 2-letter state
abbreviation, second column is full state name.
In a userform, i want the user to select the 2-letter abbreviation, and I
want to display the corresponding state name in the adjacent box (listbox?
textbox?).

If I can get help with this, I would hope to use the same method to make
other, similar linked selections available on the form.

Help?
Thanks.

Display value linked to Combo Box Selection
 

Forgot to mention: why not use a 2-column combobox?

But if for some reason you need the setup described the method I posted
should do it
--
- K Dales


"Bill" wrote:

Simple question, I think.
I have a worksheet with a two column array. First column is 2-letter state
abbreviation, second column is full state name.
In a userform, i want the user to select the 2-letter abbreviation, and I
want to display the corresponding state name in the adjacent box (listbox?
textbox?).

If I can get help with this, I would hope to use the same method to make
other, similar linked selections available on the form.

Help?
Thanks.

Display value linked to Combo Box Selection
 

use a dynamic range to name the two columns, e.g. "countries"

then use

Sub comboBox1_Change()
with me

..comboBox2.value =
application.worksheetfunction.vlookup(.combobox1.v alue,countries,2,false)

end with
End Sub


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=535571

Display value linked to Combo Box Selection
 

Thanks Matt, this method worked perfectly for me.

"MattShoreson" wrote:


use a dynamic range to name the two columns, e.g. "countries"

then use

Sub comboBox1_Change()
with me

.comboBox2.value =
application.worksheetfunction.vlookup(.combobox1.v alue,countries,2,false)

end with
End Sub


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=535571