Creating a "duplicate found msg" in VBA

[Jayliam]

I am very, very new to programming in VBA. I have come up with the following code for VBA. But, it doesn't work. I can still enter a duplicate. Can someone please share with me what I'm doing wrong?

If Trim(Me.txtheatcode.Value) = "" Then
MsgBox "Please enter a Heat Code"
If Trim(Me.txtheatcode.Value) = (Sheets("PartsData").Range("D2:D999")) = 0 Then
MsgBox "Duplicate Heat Code Found"
Me.txtheatcode.SetFocus
End If

Thank you for your time.

 

[Rick Rothstein]

The only text box I need to check for duplicates in is the txtheatcode. All the other text boxes can have duplicates. Once the user has clicked to add this information I need this program to verify there is no duplicate in column D and if there is to reject their entry while notifying them.

Describe what you mean by "reject their entry"... do you mean delete whatever text is in the textbox?

 

[Jayliam]

No I need a pop msg saying there is a duplicate record found. Until the user enters a different head code the program won't let them enter the information into the database.

 

[Rick Rothstein]

No I need a pop msg saying there is a duplicate record found. Until the user enters a different head code the program won't let them enter the information into the database.

Where is the TextBox and CommandButton located... on a worksheet or on a UserForm?

 

[Jayliam]

userform

 

[Rick Rothstein]

userform

Okay, I looked over your code again and see a problem with the way it is structured. Current you have this...

....
....
If Trim(Me.txtheatcode.Value) = "" Then
  MsgBox "Please enter a Heat Code"
  If Application.CountIf(Sheets("PartsData").Range("D2:D999"), Trim(Me.txtheatcode.Value)) Then
    MsgBox "Duplicate Heat Code Found"
    Me.txtheatcode.SetFocus
    Exit Sub
  End If
End If
....
....

but I think it should be written like this instead...

....
....
If Trim(Me.txtheatcode.Value) = "" Then
  MsgBox "Please enter a Heat Code"
  Me.txtheatcode.SetFocus
Else
  If Application.CountIf(Sheets("PartsData").Range("D2:D999"), Trim(Me.txtheatcode.Value)) Then
    MsgBox "Duplicate Heat Code Found"
    Me.txtheatcode.SetFocus
    Exit Sub
  End If
End If
....
....

Although there is a slight flaw with this... the user must enter a valid Heat Code number before he/she or your code can proceed... there is no way for the user to abort what he/she is doing. Is that acceptable to you? If not, then I can put a Cancel button on the MessageBox, but you need to tell me what should happen if the user clicks it... should the UserForm be unloaded or the TextBox cleared and the UserForm remain alive or, do you, perhaps, have some other action you would want to happen?

 

[Jayliam]

It works perfectly! With over 25,000 records I was worried about it taking a significant amount of time to search through them but excel doesn't seem to have a problem. Now I need to write a program for searching.