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: