Add Code Clear Cancel Command Buttons on User Form

Add and Code the Clear and Cancel Command Buttons on a UserForm
Today we learn how to add and code for the clear and cancel command buttons on our userform to make it more user-friendly and thereby reduce errors during data entry and improve our productivity. Also if we run the macro by mistake we can cancel the process in a more intuitive manner.
The code for all the three command buttons is given below and you’ll also notice that we have used a ‘for loop’ to clear the data from the textbox and combobox controls.
Let’s watch the video below to understand the complete process of adding and coding the new controls.


Private Sub cmdAddData_Click()
Dim RowCount As Long
Dim benefits, total As Single
If Me.txtName.Value = “” Then
MsgBox “Please enter a name”, vbExclamation, “Employee Data”
Me.txtName.SetFocus
End If
‘Range(“A5”) = txtName.Text
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox “The Amount box must contain a number.”, vbExclamation, “Employee Data”
Me.txtSalary.SetFocus
End If
‘Range(“B5”) = txtSalary.Value
benefits = txtSalary.Value * 0.5
‘Range(“C5”) = benefits
total = txtSalary.Value + benefits
‘Range(“D5”) = total
RowCount = Worksheets(“Sheet1”).Range(“A4”).CurrentRegion.Rows.Count
With Worksheets(“Sheet1”).Range(“A4”)
.Offset(RowCount, 0) = Me.txtName.Value
.Offset(RowCount, 1) = Me.ComboBox1.Value
.Offset(RowCount, 2) = Me.txtSalary.Value
.Offset(RowCount, 3) = benefits
.Offset(RowCount, 4) = total
End With
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ComboBox” Then
ctl.Value = “”
End If
Next ctl
End Sub

In the next video we’ll learn about all the ways to run our user-form automatically when we open our Excel worksheet if we wish to do so.