VBA code Using Data table to populate the userform

Question

So I am doing some coding for the payroll application. When I run the macro and enter EmployeeNumber in textbox1, I get sent to debugging and third row of code "c=application.worksheet...." is highlighted in yellow. Basically, when I enter employee number in textbox 1, the subsequent information of that employee should get populated by itself. Can someone please tell me what I am doing wrong in the code below?

Private Sub CommandButton1_Click()
Me.TextBox2.Enabled = True
Me.TextBox3.Enabled = True
Me.TextBox4.Enabled = True
Me.TextBox5.Enabled = True
Me.TextBox6.Enabled = True
Me.CommandButton2.Visible = True
Me.CommandButton1.Visible = False
End Sub

Private Sub CommandButton2_Click()
m = MsgBox("Do You Want To Update Employee Information?", vbQuestion + vbYesNo, "Confirm Update")

If m = vbNo Then Exit Sub


EmployeeNumber = Val(Me.TextBox1.Value)

c = Application.WorksheetFunction.CountIf(MasterData.Range("A:A"),  EmployeeNumber)
If c = 0 Then Exit Sub

r = Application.WorksheetFunction.Match(EmployeeNumber,   MasterData.Range("A:A"), 0)
MasterData.Range("B" & r).Value = Me.TextBox2.Value
MasterData.Range("C" & r).Value = Me.TextBox3.Value
MasterData.Range("D" & r).Value = Me.TextBox4.Value
MasterData.Range("E" & r).Value = Me.TextBox5.Value
MasterData.Range("F" & r).Value = Me.TextBox6.Value

Me.TextBox2.Enabled = False
Me.TextBox3.Enabled = False
Me.TextBox4.Enabled = False
Me.TextBox5.Enabled = False
Me.TextBox6.Enabled = False
Me.CommandButton2.Visible = False
Me.CommandButton1.Visible = True
End Sub

Private Sub CommandButton3_Click()
Unload.Me
End Sub

Private Sub Label1_Click()

End Sub

Private Sub Label2_Click()

End Sub

Private Sub Label5_Click()

End Sub

Private Sub TextBox1_Change()
Dim wks As Worksheet
Set wks = Worksheets("MasterData")

EmployeeNumber = Val(Me.TextBox1.Value)

c = Application.WorksheetFunction.CountIf(MasterData.Range("A:A"), EmployeeNumber)

If c = 0 Then
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Exit Sub
End If
r = Application.WorksheetFunction.Match(EmployeeNumber, MasterData.Range("A:A"), 0)

Me.TextBox2.Value = MasterData.Range("B" & r).Value
Me.TextBox3.Value = MasterData.Range("C" & r).Value
Me.TextBox4.Value = MasterData.Range("D" & r).Value
Me.TextBox5.Value = MasterData.Range("E" & r).Value
Me.TextBox6.Value = MasterData.Range("F" & r).Value


End Sub

Private Sub UserForm_Click()

End Sub

Answer 1

Well Apparently, the userform wasn't enabled "True/False" from properties. That was the reason, I couldn't type anything in textbox 1. I'd like to thanks DbMitch for teaching me the step by step debug approach. The form does its intended function now.

Answer 2

You can do simple troubleshooting yourself to find the problem by simplifying your code to get one item to work - then expanding the code that works.

The simplest way to troubleshoot VBA problems yourself is to add MSGBOX or DEBUG.PRINT statements to your code to trace what's happening

Currently the issue you have is that you have two locations that handle your text box - the one that's causing you a problem right now is in TextBox_Change - that's going to fire once for every character you type in.

You either want to remove that completely or move it to the TextBox AfterUpdate event - and then decide if you want to remove the duplicate code in the Command Click event

Answer 3

This must not compile - fix that first: Syntax error in

r = Application.worksheerfunction.Match

Fixed:

r = Application.WorksheetFunction.Match

EDIT: Fix more syntax errors

Replace MasaterData with MasterData

MasaterData.Range("B" & r).Value = Me.TextBox2.Value
MasaterData.Range("C" & r).Value = Me.TextBox3.Value
MasaterData.Range("D" & r).Value = Me.TextBox4.Value
MasaterData.Range("E" & r).Value = Me.TextBox5.Value
MasaterData.Range("F" & r).Value = Me.TextBox6.Value