September 3, 2017

How to Code Clear and Add Command Buttons on userform

Posted on by Dinesh Kumar Takyar

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

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

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

  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

  4. 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 !!!

  5. 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

  6. 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

  7. Hello sir, can you let me know the order of these videos from the start? This is some great educational stuff I could use!

Comments are closed.