Update Button for user form using Excel-VBA

Question

So I was trying to connect the update button to my excel sheet which contain employee's number, name and department.

How it function is the user need to input the employee's number then it will display the name and the employee's department (which I'm done with it already) Then the user can change the name as well as the department and press the update button which is where the problem comes in now.

Everytime I press the update button, it will display "Compile error: End if without block If"

Private Sub btn_Update_Click()
Dim lastRow As Integer
Dim empNum As Integer
Dim rowSelect As Integer
Dim x As Integer



If Trim(txt_EmpNo.Value) = vbNullString Then

MsgBox "The Employee's No cannot be blank!"

 Else

empNum = txt_EmpNo.Value
Sheets("LUNCH ORDER").Select

Set ws = Worksheets("LUNCH ORDER")
lastRow = ws.Cells(Rows.Count, 3).End(xlUp).Row

For x = 2 To lastRow
If ws.Cells(x, 3).Values = empNum Then Rows(x).Select
End If
Next



rowSelect = ActiveCell.Row



Cells(rowSelect, 2) = Me.txt_Name.Value
Cells(rowSelect, 3) = Me.txt_Dept.Value




End If

End Sub

Please do not overwrite your questions with a solution - that would leave us with two answers and no question, which will be confusing for future readers. Thanks. If you would like to show how you solved it, e.g. by building on the existing answer, a self-answer below is most welcome. — halfer
– halfer, Commented May 5, 2017 at 8:04
Ahh okay, sorry it's my first time here. Anyway I've put my answer below. — Joseph Lim
– Joseph Lim, Commented May 8, 2017 at 7:35

A.S.H · Accepted Answer · 2017-05-05 05:27:41Z

2

When you have the statement on the same line as the If, no End If should follow. Remove that line:

For x = 2 To lastRow
    If ws.Cells(x, 3).Values = empNum Then Rows(x).Select
    End If ' <--------------------------------------------- Remove this line
Next

Also correct this typo:

If ws.Cells(x, 3).Value = empNum Then Rows(x).Select
'                 ^^^^^^ not .Values

edited May 5, 2017 at 5:27

answered May 5, 2017 at 5:08

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joseph Lim Over a year ago

Ohh I see, however there is another error that come out, it state "Run-time error '438': Object doesn't support this property or method" Any idea how to solve it?

A.S.H Over a year ago

@JosephLim see the added part in the answer, you also had a typo .Values.

A.S.H Over a year ago

@JosephLim may be the condition is never met?

Joseph Lim Over a year ago

Thank you very much! I got it work, I put the wrong column haha

Joseph Lim · Accepted Answer · 2017-05-08 07:34:33Z

1

Thanks to A.S.H , the following code is now working.

Private Sub btn_Update_Click()
Dim lastRow As Integer
Dim empNum As Integer
Dim rowSelect As Integer
Dim x As Integer



If Trim(txt_EmpNo.Value) = vbNullString Then

MsgBox "The Employee's No cannot be blank!"

 Else

empNum = txt_EmpNo.Value
Sheets("LUNCH ORDER").Select



Set ws = Worksheets("LUNCH ORDER")
lastRow = ws.Cells(Rows.Count, 2).End(xlUp).Row



For x = 2 To lastRow
If ws.Cells(x, 2).Value = empNum Then Rows(x).Select

Next



rowSelect = ActiveCell.Row



Cells(rowSelect, 3) = Me.txt_Name.Value
Cells(rowSelect, 4) = Me.txt_Dept.Value




End If

End Sub

answered May 8, 2017 at 7:34

Joseph Lim

239 bronze badges

Collectives™ on Stack Overflow

Update Button for user form using Excel-VBA

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related