Populate a Textbox based on Combo box value.

[Stusouth]

Sorry if this really basic.

I have a very simple user form with a combo box and a text box. I can populate the combo box with a list of codes from a range. What I'm trying then to do is populate the text box a the corresponding description. ie code 1234 is Cell A3 and the description is in cell B3.
This code sits in the user form

Dim xRg As Range

Private Sub UserForm_Initialize()
Set xRg = Worksheets("Sheet1").Range("A2:B8")
Me.ComboBox1.List = xRg.Columns(1).Value
End Sub
Private Sub ComboBox1_Change()
Me.TextBox1.Text = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, xRg, 2, False)
End Sub

I can call the userform ok, but when I go and select the code in the Combo Box I get an runtime error on the Private Sub ComboBox1_Change(), 1004, unable to get the Vlookup property of the worksheetfuntion class. Please what I'm I doing wrong??

 

[Fluff]

Try

Private Sub ComboBox1_Change()
Me.TextBox1.Text = Sheets("Sheet1").Range("B" & Me.ComboBox1.ListIndex + 3)
End Sub

 

[dmt32]

try following update to code & see if helps

Dim xRg As Range

Private Sub UserForm_Initialize()
    Set xRg = Worksheets("Sheet1").Range("A2:B8")
    Me.ComboBox1.List = xRg.Columns(1).Value
End Sub

Private Sub ComboBox1_Change()
    Me.TextBox1.Value = xRg.Cells(Me.ComboBox1.ListIndex + 1, 2).Value
End Sub

Dave

 

[Stusouth]

Thankyou so much. works now. very happy.

 

[Fluff]

Not sure which of you are referring to, but glad we could help & thanks for the feedback.