Find Combobox value in range and show adjacent column value

[sharky12345]

I have a Combobox on a Userform named ComboUser - It is populated from values in range L12:L50 like this;

Dim c As Range
ComboUser.Clear
With Worksheets("Setup")
For Each c In .Range(.Range("L12"), .Range("L" & .Rows.Count).End(xlUp))
If c.Value <> vbNullString Then ComboUser.AddItem c.Value
Next c
End With

What I need is for when it changes to populate another Combobox with the value from the same row but in column N. I need this to occur whenever ComboUser is changed and without the need for the press of a button.

The values in column L can be a mixture of text and numbers, text only or numbers only if this makes any difference.

 

[MickG]

Try this:-
The code is based on combobox1 and combobox2 in Userform1
Paste code at top of Userform Module.

Option Explicit
[COLOR=navy]Dim[/COLOR] combouser [COLOR=navy]As[/COLOR] ComboBox
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Click()
    [COLOR=navy]Set[/COLOR] combouser = ComboBox1
    [COLOR=navy]With[/COLOR] combouser
        '[COLOR=green][B]Call second column of "Combouser" Combobox[/B][/COLOR]
        ComboBox2.Value = .List(.ListIndex, 1)
    [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] combouser = ComboBox1
    combouser.Clear
    [COLOR=navy]With[/COLOR] Worksheets("Setup")
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] c [COLOR=navy]In[/COLOR] .Range(.Range("L12"), .Range("L" & .Rows.Count).End(xlUp))
            [COLOR=navy]If[/COLOR] c.Value <> vbNullString [COLOR=navy]Then[/COLOR]
                combouser.AddItem c.Value
                '[COLOR=green][B]Place column "N" values in second column of Combobox[/B][/COLOR]
                combouser.List(combouser.ListCount - 1, 1) = c.Offset(, 2).Value
                '[COLOR=green][B]ComboBox2.AddItem c.Offset(, 2).Value 
                ' Not sure if you want/already have this data in combobox2.[/B][/COLOR]
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] c
    [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[sharky12345]

Thanks Mick!

 

[MickG]

You're welcome