Preventing duplicates

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
170
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
VBA Code:
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
ok,
if i type something like "Members Fees" into the combobox then click the add button on my user form it works and adds "Members Fees" to the list, then if i select "Members Fees" from the list instead of typing the word, it will create "Members Fees" Twice in my list.
 
Upvote 0
and adds "Members Fees" to the list
By "list" you mean the combobox list?
How do you populate the combobox list? using rowsource?
What I can see in your code is that it will insert the combobox value to first empty cell in col B in Sheets("Club Ledger") & col F in Sheets("PROPS").
 
Upvote 0
Col "B" in Club Ledger is where the item goes after i press add button.
Col "F" in "PROPS" is also where any new items that have been typed into the combobox are ment to go.
this part of it is working fine, except it allows duplicates to be added to Col "F" in "PROPS", i dont want this to happen, this list populates the combobox as soon as the userform is opened.
if that is easy to understand
 
Upvote 0
this list populates the combobox as soon as the userform is opened.
But how do you populate the list? using rowsource? if yes then what is the range?
What about col B, is it allowed to have duplicate?
It would help if you can upload your sample workbook (without sensitive data) to a free site such as dropbox.com & then put the link here.
 
Upvote 0
Col "B" is allowed to have duplictes as it is a financial ledger sheet.
below is the code i use to populate the combobox.

VBA Code:
Private Sub UserForm_Initialize()
Me.CmbList.List = Worksheets("PROPS").Range("f2:f30").Value

Call Count_Rows
End Sub
 
Upvote 0
Because you populate the list in UserForm_Initialize & directly use the range value then I don't see how the combobox list will change by "Sub CmdAdd_Click". Do you redefine the list somewhere in your code?
Anyway if what you need is not to insert a value that is already in the list then you can try this (but it's untested):

VBA Code:
Private Sub CmdAdd_Click()


Dim rw As Long


If CmbList = "" Then

    MsgBox "Please Add a Description!"

    CmbList.SetFocus

    Exit Sub

  End If


    If IsNumeric(Application.Match(CmbList.Value, CmbList.List, 0)) Then

    MsgBox CmbList.Value & " already exist"

    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
 
Upvote 0
ok so it wont allow a duplicate, the combobox allows me to choose Members Fees thats in the list then when i click add it shows Msgbox "Already Exsists" and does not add the the values to the "Club Ledger" worksheet, also i can no longer type a new value into the combobox, i need to do this incase the item i want is not already in the list.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top