Preventing duplicates

[Gregm66]

Hi everyone,
I am using a userform to add data to my worksheet, my userform uses a combobox to to show the list that exsists but also allows for items to be added to my list. My problem is that everytime i select or add an item it continues to create duplicates please code below that i am using, is there a way to prevent the duplicates happening on the worksheet via the code and also display a message if an item is typed in that already exsists in t

Private Sub CmdAdd_Click()

Dim rw As Long

If CmbList = "" Then
    MsgBox "Please Add a Description!"
    CmbList.SetFocus
    Exit Sub
  End If

With Sheets("Club Ledger")
    Application.ScreenUpdating = False
  
rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1

.Range("B" & rw).Value = CmbList.Value
.Range("C" & rw).Value = txtCredit.Value
.Range("D" & rw).Value = txtDebit.Value

    With Sheets("PROPS")
    
rw = .Range("F" & .Rows.Count).End(xlUp).Row + 1

.Range("F" & rw).Value = CmbList.Value

    Application.ScreenUpdating = True

    End With
End With
'================================
  'Clear the text boxes
  '================================
  CmbList.Value = ""
  txtCredit.Value = ""
  txtDebit.Value = ""
End Sub

 

[Akuini]

When you type a new value then click "CmdAdd_Click", did the message " already exist" also pop up?

 

[Gregm66]

Yes it did, stops me going any further.

 

[Akuini]

Hm, that's strange. I don't know why that happen.
Can you upload your sample workbook to dropbox.com?

 

[Gregm66]

sorry i dont have a sample one the workbook i am using is my actual workbook full of sensitive info.
i will try another way around this. but thankyou for your help

 

[Akuini]

Ok, if you want you can see my example here:

try223.xlsm

Shared with Dropbox

www.dropbox.com

see how it works

 

[Gregm66]

you one works it allows me to add extra "Items or Names" to the list.
Now all i need to do is get it to add the "Name or Item" to Sheet2 without it showing the MsgBox "Already Exsists" if you understand that

 

[Akuini]

Now all i need to do is get it to add the "Name or Item" to Sheet2 without it showing the MsgBox "Already Exsists" if you understand that

I don't quite understand. In my example sheet1 is "PROPS" in your workbook, it is where you get the list for the combobox, then what is sheet2 in your workbook?
Say "Tom" is already in the combobox list then it's possible that you want to add "Tom" to sheet2 ?
I think I misunderstood what you mean by duplicate, do you mean you want to prevent duplicate in Col "F" in "PROPS"?
But because you populate the combobox list from column F: Me.CmbList.List = Worksheets("PROPS").Range("f2:f30").Value then if you insert a value from the combobox list it will then become a duplicate in col F, doesn't it?

 

[Gregm66]

ok i will try to re explain...
I have 2 Sheets, 1 Called "PROPS" this sheet only holds my lists.
2nd sheet is called "Club Ledger"

So when i open "Club Ledger" i click a button that opens my user form to add data to the "Club Ledger" worksheet.,
the UserForm has 1 ComboBox, and 2 TextBoxes. Text boxs hold Dollar Values Only.
Once i have selected IE: Members Fees or something else i then add a dollar value to 1 of the textboxs only.
then i click on the add button, if all goes well it then adds what i have put in the combobox and textbox to the "ClubLedger" Worksheet.
it is also ment to add what is in the combobox to the "PROPS" worksheet. But only if the item that is in the Combobox is not in the "PROPS" worksheet list. so in essance i should also be able to type a new name in the combobox and it would add it to the list if it does not exsist there already.
i hope i have not confused you

 

[Gregm66]

If you replace your UserForm code in your sample book you sent me with the following code you you will see what i mean..

Private Sub CommandButton1_Click()

Dim rw As Long

If ComboBox1 = "" Then
    MsgBox "Please Add a Description!"
    ComboBox1.SetFocus
    Exit Sub
  End If
 
If IsNumeric(Application.Match(ComboBox1.Value, ComboBox1.List, 0)) Then
    MsgBox "already exist"
    'Call Add_Data
    'Exit Sub
End If
With Sheets("Sheet2")
    Application.ScreenUpdating = False
    'Application.EnableEvents = False
rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1

.Range("A" & rw).Value = ComboBox1.Value
.Range("B" & rw).Value = TextBox1.Value
.Range("C" & rw).Value = TextBox2.Value

End With

With Sheets("Sheet1")

If IsNumeric(Application.Match(ComboBox1.Value, ComboBox1.List, 0)) Then
    
rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1

.Range("A" & rw).Value = ComboBox1.Value

    'Application.EnableEvents = True
    Application.ScreenUpdating = True

End If
End With
'================================
  'Clear the text boxes
  '================================
  ComboBox1.Value = ""
  TextBox1.Value = ""
  TextBox2.Value = ""

End Sub

 

[Akuini]

Ok, here's my understanding:
If the combobox value is not in the combobox list then you can insert the value to both sheets.
But if the combobox value is already in the combobox list (which also means already in "PROPS" list, right? because they are the same list) then you can insert it to "Club Ledger" but not to "PROPS".
Is that correct?