userform stopping duplication

A

~Alan

XL2000 userform
is there something I can add to this code that will stop duplication of
an item.

Private Sub cmdAdd_Click()
Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Parts")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value
txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
txtParts.SetFocus
End Sub
 
D

Dave Peterson

if application.countif(ws.range("a:a"),txtpartnumber.value) > 0 then
'don't do it, it's already there
beep
msgbox "Duplicate"
else
'do all your assignments
end if

(what out for typos!)
 
D

Dave Peterson

You're gonna have to share a little more info on why it doesn't work.

But if you combined it with the previous code, maybe the followup suggestion
will help.
 
A

~Alan

I am so in barest I have a red face mane on man :)
Somehow I put the code in twice and I was editing the wrong code
that is why nothing worked
Thank you again for your patience and your help
 
D

Dave Peterson

You ain't the first. (I hope I wasn't the first, either! <vbg>)

~Alan said:
I am so in barest I have a red face mane on man :)
Somehow I put the code in twice and I was editing the wrong code
that is why nothing worked
Thank you again for your patience and your help
Click to expand...
 
A

~Alan

When i enter a Duplicate character it gives me the message "Duplicate"
and also Duplicates
the characters
Question how do I stop it from duplicating the characters
 
D

Dave Peterson

When you used this kind of code:


did you put all your assignments in the ELSE portion?

(and you can remove the msgbox when you're done testing)

Private Sub cmdAdd_Click()
Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Parts")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With


if application.countif(ws.range("a:a"),txtpartnumber.value) > 0 then
'don't do it, it's already there
beep
'msgbox "Duplicate"
else
'do all your assignments
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value
txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
txtParts.SetFocus
end if
End Sub

I'm not sure when you want to update the userform.

maybe
Private Sub cmdAdd_Click()
Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Parts")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With


if application.countif(ws.range("a:a"),txtpartnumber.value) > 0 then
'don't do it, it's already there
beep
'msgbox "Duplicate"
else
'do all your assignments
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value
end if

txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
txtParts.SetFocus

End Sub
 
A

~Alan

This is the code verbatem,
it lets me know that it is a duplicate, and it also duplicates it in
sheet(parts).
is there a code I can put in sheet(parts) that will stop from
duplicating any item
Also the only way I can get this code to work is to 'txtPart.SetFocus.
I am not sure what txtPart.SetFocus does could this be me problem?

Private Sub cmdAdd_Click()
Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Parts")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
If Application.CountIf(ws.Range("a:a"), txtPartnumber.Value) > 0 Then
'don't do it, it's already there
Beep
MsgBox "Duplicate"
Else
'do all your assignments
End If
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value
With Worksheets("sheet2")
.Cells(.Rows.Count, "DR").End(xlUp).Offset(1, 0).Value _
= txtPartnumber.Value
End With
txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
'txtPart.SetFocus

End Sub
 
D

Dave Peterson

I'm not sure what should be done with all that stuff, but if you put what you
want to happen within the if/then/else structure, you'll be set:

Option Explicit

Private Sub cmdAdd_Click()
Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Parts")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
If Application.CountIf(ws.Range("a:a"), txtPartnumber.Value) > 0 Then
'don't do it, it's already there
Beep
'MsgBox "Duplicate"
Else
'do all your assignments 'do all this stuff or just part of it??????

r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value
With Worksheets("sheet2")
.Cells(.Rows.Count, "DR").End(xlUp).Offset(1, 0).Value _
= txtPartnumber.Value
End With
txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
'txtPart.SetFocus
End If
End Sub

~Alan said:
This is the code verbatem,
it lets me know that it is a duplicate, and it also duplicates it in
sheet(parts).
is there a code I can put in sheet(parts) that will stop from
duplicating any item
Also the only way I can get this code to work is to 'txtPart.SetFocus.
I am not sure what txtPart.SetFocus does could this be me problem?

Private Sub cmdAdd_Click()
Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Parts")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
If Application.CountIf(ws.Range("a:a"), txtPartnumber.Value) > 0 Then
'don't do it, it's already there
Beep
MsgBox "Duplicate"
Else
'do all your assignments
End If
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value
With Worksheets("sheet2")
.Cells(.Rows.Count, "DR").End(xlUp).Offset(1, 0).Value _
= txtPartnumber.Value
End With
txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
'txtPart.SetFocus

End Sub
Click to expand...
 
A

~Alan

It inserts the info from the userform to sheet(parts) even though I am
notified that it is a duplicate.

If I have a sheet several rows long with part numbers in column "A"
is there a code that if I was to enter a duplicate number somewhere else
up or down the sheet
it wont let me. As in copy, sort, no duplicates,

If Application.CountIf(ws.Range("a:a"), txtPartnumber.Value) > 0 Then
'don't do it, it's already there
Beep
MsgBox "Duplicate"
Else
'do all your assignments 'do all this stuff or just part of it??????
 
D

Dave Peterson

Even after you moved your last "End if" closer to the bottom of your code?

That was the single change I suggested. It could have been easily missed.
 
A

~Alan

I moved the whole if statement not "End if"
after taking my time a rereading your last message
i finally understood what you meant.
Once again thank you for your patience and understanding.
yepeee it works
 
D

Dave Peterson

Glad you finally got it working!

~Alan said:
I moved the whole if statement not "End if"
after taking my time a rereading your last message
i finally understood what you meant.
Once again thank you for your patience and understanding.
yepeee it works
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

userform to 2 seperate sheets 4
userform 3
one more text box to a userform 1
Run time Error - Application Defined or Object Defined Error Excel Vba 0
enable re-code to re-read on re-hidden columns, if can be possible 6
a macro with finishing criteria 2
Why am I getting this VBA error? 2
MACRO COLUMNS, SKIP ROWS 6

Top