UserForm and VBA Vlookup Functions - help!

[Alex Simmons]

Hi, am new here and would like some assistance please.

I am having some trouble with getting lookup functionality working in a userform.

Not being an experienced programmer, it's driving me nuts.

I've searched a lot for examples but can't seem to nail something that works how I'd like.

I have a basic client list of information:
Name, ***, date of birth, commencement date, expiry, service level etc etc.

Attached is a basic workbook. The first sheet is called "ClientData" and is a complete list of the data. There are two dynamic named ranges. One covers the entire table of data, the other is just the first column with ClientName. This latter dynamic list is intended for the rowsource for either a combo box or a listbox in the userform (I'm not having much luck with either).

There is a basic userform set up with value field for each item to be displayed.

What I'm trying to do is have the user choose from a list of client names in the combo box (or a listbox) using the dynamic range list of client names, and then for the rest of the form to update with all the corresponding data from the table.

This is the code attached to the combo box and intended to look up the result from the user's selection and populate textbox1 with the correct data from the table, in this case the client's name, but ultimately I intend to have it populate all the fields in the userform.

Private Sub ComboBox1_Change()
Dim answer As String
answer = ComboBox1.Value
 TextBox1.Value = WorksheetFunction.VLookup(answer, _
  Sheets("ClientData").Range("ClientListTable"), 1, False)

End Sub

I've tried a stack of variations on a theme but either I get errors or it hangs.
(am using Win7, MSXL 2010)

Ultimately I'd like to set up user forms for many of the user interactions which are presently managed directly on sheets as I'm attempting to set up a more professional UI. Adding/editing clients, bringing other stored data into various detailed planning sheets I already have on so on. But I seem stuck on what seems to be a pretty basic feature.

Any helpful guidance appreciated.