Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Preventing duplicates in Excel VBA

Preventing duplicates in Excel VBA

Preventing duplicates in Excel VBA

I am working with a user form that I need to check for duplicates and then return a message to the user if an item already exsists.

Any help would be greatly appreciated.

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long

Dim ws As Worksheet
Set ws = Worksheets("Items")

'find first empty row in database
''lRow = ws.Cells(Rows.Count, 1) _
'' .End(xlUp).Offset(1, 0).Row

'revised code to avoid problems with
'Excel lists and tables in newer versions
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'Verifying Customer
If Trim(Me.cboCustomer.Value) = "Select" Or Me.cboCustomer.Value = "" Then
MsgBox "Need a customer...duh!"
Exit Sub
End If

'Verifying Item
If Trim(Me.txtItem.Value) = "" Then
MsgBox "What good is a new item without an item number?"
Exit Sub
End If

'Verifying Unit of Measure
If Trim(Me.cboUoM.Value) = "Select" Or Me.cboUoM.Value = "" Then
MsgBox "Kind of need to know how to count this new item..."
Exit Sub
End If

'Prevent duplicates

'copy the data to the database

With ws
.Cells(lRow, 1).Value = Me.cboCustomer.Value
.Cells(lRow, 2).Value = Me.txtItem.Value
.Cells(lRow, 3).Value = Me.txtdesc.Value
.Cells(lRow, 4).Value = Me.cboUoM.Value
End With

'clear the data
Me.cboCustomer.Value = "Select"
Me.txtItem.Value = ""
Me.txtdesc.Value = ""
Me.cboUoM.Value = "Select"

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

RE: Preventing duplicates in Excel VBA

Why did you post another thread on this subjuct?


glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close