Userform: Populating textboxes based on combobox value [duplicate]

Question

I'm hoping there's going to be a simple explanation for this I have a userform that allows the user to select a unique id once this selected it then populates several textboxes with information about the individual. I have been trying to use vlookup to achieve this but i keep getting run-time 1004 error.

The code i'm currently using is

Private Sub UserForm_Initialize()

ComboBox1.List = Worksheets("Data").Range("A2:A5000").Value

End Sub

Private Sub ComboBox1_Change()

TextBox1.Value = WorksheetFunction.VLookup(ComboBox1.Value, Worksheets("Data").Range("A2:N5000"), 2, False)

End Sub

I am looking to include the values/text from columns B,C,E,F,I,J & K within text boxes on the userform.

The data has a lot of personal information therefore I have not included it at the moment.

Thanks in advance

Answer 1

It appears that the range of data is static while the form is active, no new lines, no deleted lines. IF this is true then simplify the code with direct addressing:

    dim idx as integer
    ...
    idx = ComboBox1.ListIndex
    if idx < 0 then exit sub
    ' idx is zero based, so add 2 to point to correct row
    textBox1.value = worksheets("data").cells(idx + 2, 2).value