vlookup in userform displayed in textbox (and combo box woes)

[drex]

Hello,

After hours of countless related threads, I still cannot discover ways to vlookup with userform's textboxes to display information from a separate sheet based on a vlookup reference inside another textbox.

I'll simplify things;

A button is pressed on a sheet named "input";

Sub clickme()
coaching.Show False
End Sub

the following form pops up;

The only input required from the user is the "Agent ID:" text box (named txtid). I need txtid to be my vlookup reference. The remaining fields should autocomplete (Agent Name:, Team Leader:, LOB.

The information is then sent to a sheet named "data_coaching";

Option Explicit
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("coaching_data")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'error on blank id
If Trim(Me.txtid.Value) = "" Then
  Me.txtid.SetFocus
  MsgBox "Something went wrong. The cake is a lie!"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtid.Value
ws.Cells(iRow, 3).Value = Me.txtname.Value
ws.Cells(iRow, 4).Value = Me.txttl.Value
ws.Cells(iRow, 5).Value = Me.txtlob.Value

'clear the data
Me.txtid.Value = ""
Me.txtname.Value = ""
Me.txttl.Value = ""
Me.txtlob.Value = ""
Me.txtid.SetFocus

End Sub
Private Sub cmdReset_Click()
'clear the data
Me.txtid.Value = ""
Me.txtname.Value = ""
Me.txttl.Value = ""
Me.txtlob.Value = ""
Me.txtid.SetFocus
End Sub
Private Sub cmdClose_Click()
  Unload Me
End Sub

The information should be vlookup'ed from a separate sheet named "agent_data" seen here:

I also have a combobox ("Submitter:") that should pull info from a sheet named "staff". Column A contains a list of employees and Column B contains a second list. The checkbox ("CCR") should, when TRUE, switch from A to B.

Nothing I have tried will work mainly due to my extreme lack of VBA knowledge.

Would this code be placed in a module? the form itself?

Can anyone shed some light on vlookups, userforms and comboboxes?

PS - the cake IS a lie!

 

[AlphaFrog]

I couldn't test this but it should point you in the right direction I hope.

Private Sub UserForm_Initialize()

    'default cboSubmitter list: Populate cboSubmitter from sheet "staff" from A2 to last used cell in column A
    With Worksheets("staff")
        Me.cboSubmitter.RowSource = "staff!" & .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Address
    End With
    
End Sub


Private Sub cbCCR_Change()
    ' On checkbox CCR change, toggle the list for cboSubmitter to sheets "staff" columns A and B
    With Worksheets("staff")
        If Me.cbCCR.Value Then
            Me.cboSubmitter.RowSource = "staff!" & .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Address
        Else
            Me.cboSubmitter.RowSource = "staff!" & .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Address
        End If
    End With
End Sub


Private Sub cboSubmitter_Change()
    
End Sub


Private Sub txtID_Change()
    Dim r As Long
    
    With Worksheets("agent_data")
    
        'Clear other fields if ID is deleted
        If Trim(Me.txtID.Value) = "" Then
            'clear the data
            Me.txtname.Value = ""
            Me.txttl.Value = ""
            Me.txtlob.Value = ""
            Me.txtID.SetFocus
            Exit Sub
        End If
        
        'Populate other fields on txtID lookup
        'Test if txtID has a lookup match
        If WorksheetFunction.CountIf(.Columns("A"), Trim(Me.txtID.Value)) > 0 Then
            'Match exists. Find row and populate other fields
            r = WorksheetFunction.Match(CLng(Trim(Me.txtID.Value)), .Columns("A"), 0)
            Me.txtname.Value = .Cells(r, "B")
            Me.txttl.Value = .Cells(r, "C")
            Me.txtlob.Value = .Cells(r, "D")
            Me.txtID.SetFocus
        Else
            'No ID match
            Me.txtname.Value = "No ID match"
            Me.txttl.Value = "No ID match"
            Me.txtlob.Value = "No ID match"
            Me.txtID.SetFocus
            Exit Sub
        End If
    End With

End Sub