Macro using Vlookup

Take a look here and see if you get some ideas.

http://www.contextures.com/Excel-VBA...Box-Lists.html

I was doing some more playing around with this and came up with some code which I believe may work for you.

I'm guessing you are trying to return the total accumulated amount for either James or John? If this is the case then it seems you would want to use a Sumif function versus the Vlookup which will only return one result, not a total.

So here a two pieces of code to use.

The first uses advanced filter to make a unique list for the combobox and the second will populate textbox1 on change of combobox1. Unique list will be created in column L.

I figured using the code provided you could probably figure out how to add to the code for the second textbox.

This is just one way to do it, but I'm sure if you poked around more you could find others. I hope this helps.

Private Sub UserForm_Initialize()
    With Sheet1
        .Range(.Range("E3"), .Range("E" & .Rows.Count).End(xlUp)) _
        .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("L3"), Unique:=True
        ComboBox1.List = .Range(.Cells(4, 12), .Cells(.Rows.Count, 12).End(xlUp)).Value
    End With
    Me.ComboBox1.SetFocus
End Sub

Private Sub ComboBox1_Change()
    Dim CL As Range
    Dim AA As Range
    Dim CLR As String
    With Sheet1
        Set CL = .Range("E4", .Cells(.Rows.Count, "E").End(xlUp))
        Set AA = .Range("J4", .Cells(.Rows.Count, "J").End(xlUp))
        Frm1 = Application.WorksheetFunction.SumIf(CL, ComboBox1, AA)
        frmMain.TextBox1 = Frm1
    End With
End Sub