How to Code Clear and Add Command Buttons on userform

How to Code Clear and Add Command Buttons on user-form to clear data and add data while avoiding duplicate entries. Watch the video:

 

Watch this video on YouTube.

 

Here’s the complete VBA code:

Dim currentrow As Long

Private Sub cmdAdd_Click()
Dim lastrow As Long, count As Long

lastrow = Sheet1.Cells(Rows.count, 1).End(xlUp).Row
lastrow = lastrow + 1
Cells(lastrow, 1) = TextBox1
count = 0
For i = 2 To lastrow
If TextBox1 = Cells(i, 1) Then
count = count + 1
End If

If count > 1 Then
Cells(lastrow, 1) = “”
Cells(lastrow, 2) = “”
Cells(lastrow, 3) = “”
MsgBox “Duplicate entry! Name already exists!”
End If
If count = 1 Then
Cells(lastrow, 1) = TextBox1.Text
Cells(lastrow, 2) = TextBox2.Text
Cells(lastrow, 3) = TextBox3.Value
End If
Next
End Sub

Private Sub cmdClear_Click()
‘TextBox1 = “”
‘TextBox2 = “”
‘TextBox3 = “”
Dim ctl As Control

For Each ctl In UserForm1.Controls
If TypeName(ctl) = “TextBox” Then
ctl.Value = “”
End If
Next ctl

End Sub

Private Sub UserForm_Initialize()

currentrow = 2
TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)

End Sub

Further reference:

Create and Manage Database in Excel with User-Form

3 thoughts on “How to Code Clear and Add Command Buttons on userform

  1. Juan

    excellent, it is seen that you are an expert in the field, you only need to share the examples you present, congratulations

    Reply
  2. Pingback: Add Data to Excel Database with UserForm using Countif Function | Excel VBA Training Online

  3. Pingback: Search for Data In Excel Database with UserForm | Excel VBA Training Online

Leave a Reply

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