Preventing duplicates

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
165
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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,257
Office Version
2013
Platform
Windows
and also display a message if an item is typed in that already exsists in t
In where?
Can you explain in more detail what you consider as duplicate?
 

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
165
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.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,257
Office Version
2013
Platform
Windows
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").
 

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
165
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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,257
Office Version
2013
Platform
Windows
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.
 

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
165
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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,257
Office Version
2013
Platform
Windows
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
 

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
165
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.
 

Forum statistics

Threads
1,078,253
Messages
5,339,110
Members
399,279
Latest member
danidanidaniel

Some videos you may like

This Week's Hot Topics

Top