learn microsoft excel

Add and Code the Clear and Cancel Command Buttons on a UserForm

Excel 2007
Excel 2003
Macros Excel 2007
Macros Excel 2003
Excel 2010
About Us
Free Ebook
Offline Training

Interactive Training

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.

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Interactive Training