userform stopping duplication

  • Thread starter Thread starter ~Alan
  • Start date Start date
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
 
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!)
 
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.
 
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
 
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...
 
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
 
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
 
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
 
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...
 
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??????
 
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.
 
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
 
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
Why is this code not working ? 3
enable re-code to re-read on re-hidden columns, if can be possible 6
a macro with finishing criteria 2
MACRO COLUMNS, SKIP ROWS 6

Back
Top