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

 

[Gregm66]

Yes exactly that is correct

 

[Akuini]

OK try this one:

Private Sub CmdAdd_Click()


Dim rw As Long


If CmbList = "" Then

    MsgBox "Please Add a Description!"

    CmbList.SetFocus

    Exit Sub

End If

Application.ScreenUpdating = False

With Sheets("Club Ledger")

    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


End With

    If IsNumeric(Application.Match(CmbList.Value, CmbList.List, 0)) Then
       
       MsgBox CmbList.Value & " already exist"

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

    End If

Application.ScreenUpdating = True

'================================

  'Clear the text boxes

  '================================

  CmbList.Value = ""

  txtCredit.Value = ""

  txtDebit.Value = ""

End Sub

 

[Gregm66]

when i type a new entry into the combobox i get an error, "Invalid Property Value"
so i take it that it wont allow me to add a new item

 

[Gregm66]

All good Akuini,
It was a setting in propertys i just fixed it, now it is working 100% Correctly
Thankyou very much for all of your help, Problem solved....

Cheers Thankyou

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.
One thing though:
I think you need to repopulate the combobox list after inserting a new value in colomn F.
Maybe something like this:
Just add the blue line.

        With Sheets("PROPS")


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

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

            Me.CmbList.List = Worksheets("PROPS").Range("f2:f" & rw + 1).Value


        End With