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:
excellent, it is seen that you are an expert in the field, you only need to share the examples you present, congratulations
I copied your code to past in my VBA project, but it errors.
Compile error:
Can’t find project or library
and It highlight on “i”
Here : (For i = 2 To lastrow)
Pls kindly help me !!!
can u help me, I bit confuse on the variables show which I always get error in clear, add and search which it not working and I already check the each code one by one and the name which it could be misspelled but still has error
lastrow = Sheet1.Cells(Rows.count, 1).End(xlUp).Row
i changed sheet1 to my sheet name but it still tells me i have an error in it.
Please Dinesh help me out! Thanks
Hello sir, can you let me know the order of these videos from the start? This is some great educational stuff I could use!
Thank you sir