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.

6 thoughts on “Add Code Clear Cancel Command Buttons on User Form

  1. Harsh

    Hi Sir,

    I was going through this video, and i have created a clear button on my userform, and have done the coding as mentioned by you, but still my clear button is not working.

    This is the code that i have entered.

    Private Sub clearbutton_Click()
    Dim ctl As Control
    For Each ctl In bill_generator.Controls
    If TypeName(ctl) = “TextBox” Then ctl.Value = “”
    End If
    Next ctl
    End Sub

    Please guide me where i m wrong in this.

    Thank you for your help.

    Reply
  2. Harsh

    Thank You sir for your response.

    But somehow the Clear button is still not performing the task as per the code you mentioned.

    Please guide where i m going wrong. These are the three buttons i have. and the codes for them.

    Private Sub cancelbutton_Click()
    Unload bill_generator
    End Sub

    Private Sub clearbutton_Click()
    Dim ctl As Control
    For Each ctl In bill_generator.Controls
    If TypeName(ctl) = “TextBox” Then
    ctl.Value = “”
    Next ctl
    End Sub

    Private Sub generatebillbutton_Click()
    End
    End Sub

    Thank you for the help.

    Reply
    1. Dinesh Kumar Takyar Post author

      Try this:
      For Each ctl In Me.Controls
      Hopefully your UserForm name is ‘bill_generator’
      By the way I ran your code on my computer and it worked perfectly! The user-form name in the code must be 100% correct.

      Reply
  3. chander shekhar 9779040472

    please decode vba code of itr1 sahaj excel utility availble athttps://incometaxindiaefiling.gov.in/ and intimate by email

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *