User form code issues, code clean up

Question

I have an error with the code below. What I am trying to do is type info in the textbox then when I go to the next box it will auto fill the next textboxes with the info that is in the next cells of my database. Anything will be of help.

Private Sub TextBox6_AfterUpdate()

  If WorksheetFunction.CountIf(Mastor_list.Range(Columns("D:D")), Me.TextBox6.Value) = 0 Then
        Me.TextBox6.Value = ""
        Exit Sub
    End If

    With Me
        .TextBox1 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox6), Mastor_list.Range("Lookup"), 5, 0)
        .TextBox2 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox6), Mastor_list.Range("Lookup"), 6, 0)
        .TextBox3 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox6), Mastor_list.Range("Lookup"), 7, 0)
        .TextBox4 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox6), Mastor_list.Range("Lookup"), 8, 0)
        .TextBox5 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox6), Mastor_list.Range("Lookup"), 10, 0)
    End With
End Sub

OK so now I have the first part working but when I try to match something with my VLookup its not working I am getting ether a syntax error or a Compile error: Expected: end of statement error

Private Sub TextBox6_AfterUpdate()

Sheets("Mastor list").Select
ActiveSheet.Unprotect
If WorksheetFunction.CountIf(Sheets("Mastor list").Range("D:D", Range("D:D").End(xlDown)), Me.TextBox6.Value) = 0 Then

    Exit Sub
End If

With Me
Sheets("Mastor list").Select
    TextBox1.Text= "WorksheetFunction.VLookup(Me.TextBox6),Mastor list.Range(Mastor list.Columns("D:D")), 5, 0)"

Sheets("Mastor list").Select
ActiveSheet.Protect


End Sub

Answer 1

The way you are specifying your ranges is not working. See the following:

Private Sub TextBox6_AfterUpdate()

    If WorksheetFunction.CountIf(Sheets("Mastor_list").Range("D1", Range("D1").End(xlDown)), Me.TextBox6.Value) = 0 Then
        Me.TextBox6.Value = ""
        Exit Sub
    End If

    With Me
        .TextBox1 = WorksheetFunction.VLookup(CLng(Me.TextBox6), Sheets("Mastor_list").Range("Lookup"), 5, 0)
        .TextBox2 = WorksheetFunction.VLookup(CLng(Me.TextBox6), Sheets("Mastor_list").Range("Lookup"), 6, 0)
        .TextBox3 = WorksheetFunction.VLookup(CLng(Me.TextBox6), Sheets("Mastor_list").Range("Lookup"), 7, 0)
        .TextBox4 = WorksheetFunction.VLookup(CLng(Me.TextBox6), Sheets("Mastor_list").Range("Lookup"), 8, 0)
        .TextBox5 = WorksheetFunction.VLookup(CLng(Me.TextBox6), Sheets("Mastor_list").Range("Lookup"), 10, 0)
    End With
End Sub

You will want to include some error handling because, if any of your VLookups don't find a match, an error will be thrown.