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