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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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

Leave a Reply

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