Vlookup in form.

[excelkeechak]

hi,,

i had created a user form where i want to show the values in the listbox based on values from combobox.i had used the vlookupfunction to get so.I m getting an error.Is my syntax for the function incorrect..kindly tell me...

[FDibbins]

I can find no formulas or anything in your workbook, just 3 plain columns of info. Did you upload the correct file?

[royUK]

Your VLOOKUP fomula wouldn't work even in a sheet, the Lookup value should be in the first column of the lookup table
Rename tab to be =Sheet1!$B$2:$B$6, note how the range is referred to in the code.

Private Sub ComboBox1_Change()
ListBox1.AddItem Worksheetfunction.VLookup(ComboBox1.Value, Range("tab"), 2, 0)
End Sub

A better way would be to use

Private Sub ComboBox1_Change()
ListBox1.AddItem Cells(ComboBox1.ListIndex + 2, 3).Value
End Sub

[excelkeechak]

Thanks mr.Roy..the second code works.but the list box is not cleared when a new selection is made in the combobox....?

[royUK]

It's a simple fix & you should check out control properties in the Object Browser if you want to learn VBA

Private Sub ComboBox1_Change()
Listbox1.Clear
Listbox1.AddItem Cells(ComboBox1.ListIndex + 2, 3).Value
End Sub

[excelkeechak]

thanks roy....