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.

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.value,countries,2,false)

end with
End Sub

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.

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.

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.value,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
>
>