How to use vba vlookup formula from two sheets?

Question

I have two sheets first one called Sheet8 is for the Main table that can be used by the Data Entry Form to enter the data in that table, and the second called Sheet9 that includes the table for the vlookup. What I want is in the data entry user form as soon as I enter the Name the Discipline is created automatically based on that name.

Screenshot of the Data Entry for the Main table in sheet8

Screenshot of the sheet9 Table

The code for Save Button

Private Sub btnSave_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet8")
Dim n As Long

n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

sh.Unprotect "1234"

sh.Range("A" & n + 1).Value = Me.txtDate.Value
sh.Range("B" & n + 1).Value = Me.txtName.Value
sh.Range("C" & n + 1).Value = Me.txtProjNo.Value
sh.Range("D" & n + 1).Value = Me.txtProjTitle.Value
sh.Range("E" & n + 1).Value = Me.txtBVEntity.Value
sh.Range("F" & n + 1).Value = Me.txtZIG.Value
sh.Range("G" & n + 1).Value = Me.txtSpenthrs.Value
sh.Range("H" & n + 1).Value = Me.comboCategory.Value
sh.Range("I" & n + 1).Value = Me.txtDiscipline.Value
sh.Range("J" & n + 1).Value = Me.txtSCV.Value
sh.Range("K" & n + 1).Value = Me.txtTotSCV.Value
sh.Range("L" & n + 1).Value = Me.txtCotMER.Value
sh.Range("M" & n + 1).Value = Me.txtBudgethrs.Value
sh.Range("N" & n + 1).Value = Me.txtBudget.Value
sh.Range("O" & n + 1).Value = Me.txtProgress.Value
sh.Range("P" & n + 1).Value = Me.txtEndDate.Value


sh.Protect "1234"

The code for the Name textbox

Private Sub txtName_AfterUpdate()

If WorksheetFunction.CountIf(Sheet9.Range("C:D"), Me.txtName.Value) = 0 Then
MsgBox "This Name is Invalid."
Me.txtName.Value = ""
Exit Sub
End If

With Me
.txtDiscipline = Application.WorksheetFunction.VLookup(Me.txtName, Sheet9.Range("Lookup"), 4, 0)
End With

End Sub