Prevent duplicate entry in userform in excel vba

[lovejanu4u]

I am using command button to add data, but i want that code should also have duplicate entries restriction included in it.

Please check my code and help me finding the proper solution.

Thanks q in advance.

Dim x As Long
Dim y As Worksheet
Set y = Sheets("PRC Training Database")
x = y.Range("B" & Rows.Count).End(xlUp).Row
With y
.Cells(x + 1, "B").Value = TextBox1.Text
.Cells(x + 1, "c").Value = TextBox2.Text
.Cells(x + 1, "D").Value = TextBox3.Text
.Cells(x + 1, "E").Value = TextBox4.Text
.Cells(x + 1, "F").Value = TextBox5.Text
.Cells(x + 1, "G").Value = TextBox6.Text
.Cells(x + 1, "H").Value = TextBox7.Text
.Cells(x + 1, "I").Value = TextBox8.Text
.Cells(x + 1, "J").Value = TextBox9.Text
.Cells(x + 1, "K").Value = TextBox10.Text
.Cells(x + 1, "M").Value = TextBox11.Text
.Cells(x + 1, "N").Value = TextBox12.Text
.Cells(x + 1, "P").Value = TextBox13.Text
.Cells(x + 1, "Q").Value = TextBox14.Text
.Cells(x + 1, "S").Value = TextBox15.Text
.Cells(x + 1, "T").Value = TextBox16.Text
.Cells(x + 1, "V").Value = TextBox17.Text
.Cells(x + 1, "W").Value = TextBox18.Text
.Cells(x + 1, "Y").Value = TextBox19.Text
.Cells(x + 1, "Z").Value = TextBox20.Text
.Cells(x + 1, "AB").Value = TextBox21.Text
.Cells(x + 1, "AC").Value = TextBox22.Text
.Cells(x + 1, "AE").Value = TextBox23.Text
.Cells(x + 1, "AF").Value = TextBox24.Text
.Cells(x + 1, "AH").Value = TextBox25.Text
.Cells(x + 1, "AI").Value = TextBox26.Text
.Cells(x + 1, "AK").Value = TextBox27.Text
.Cells(x + 1, "AL").Value = TextBox28.Text
.Cells(x + 1, "AN").Value = TextBox29.Text
.Cells(x + 1, "AO").Value = TextBox30.Text
.Cells(x + 1, "AQ").Value = TextBox31.Text
.Cells(x + 1, "AR").Value = TextBox32.Text
.Cells(x + 1, "AT").Value = TextBox33.Text
.Cells(x + 1, "AU").Value = TextBox34.Text
.Cells(x + 1, "AW").Value = TextBox35.Text
.Cells(x + 1, "AX").Value = TextBox36.Text
.Cells(x + 1, "AZ").Value = TextBox37.Text
.Cells(x + 1, "BA").Value = TextBox38.Text
.Cells(x + 1, "BC").Value = TextBox39.Text
.Cells(x + 1, "BD").Value = TextBox40.Text
.Cells(x + 1, "BF").Value = TextBox41.Text
.Cells(x + 1, "BG").Value = TextBox42.Text
.Cells(x + 1, "BI").Value = TextBox43.Text
.Cells(x + 1, "BJ").Value = TextBox44.Text
.Cells(x + 1, "BL").Value = TextBox45.Text
.Cells(x + 1, "BM").Value = TextBox46.Text
.Cells(x + 1, "BO").Value = TextBox47.Text
.Cells(x + 1, "BP").Value = TextBox48.Text

 

[dmt32]

Hi,
welcome to the forum.

I assume that one of your columns holds a unique entry maybe something like staff ID? if so, can you let us know which column it is otherwise, explain a little further which data you need to check for duplicates

Dave

 

[mikerickson]

How are you defining "duplicate". Is there a column where a duplication of that one column makes the whole record a duplicate? Or must all the the columns entries be the same in order to be considered a duplicate?

 

[lovejanu4u]

Yes, its ID.No with textbox1 which is in B column.

 

[lovejanu4u]

Yes, its ID.No with textbox1 which is in B column.

How are you defining "duplicate". Is there a column where a duplication of that one column makes the whole record a duplicate? Or must all the the columns entries be the same in order to be considered a duplicate?

 

[dmt32]

Yes, its ID.No with textbox1 which is in B column.

Hi,
untested but try this update to your code & see if helps

Private Sub CommandButton1_Click()
    Dim c As Integer, i As Integer, CheckColumn As Integer
    Dim x As Long
    Dim CheckDuplicate As String
    Dim y As Worksheet
    
    Set y = Sheets("PRC Training Database")
    
'value you are checking
    CheckDuplicate = Me.TextBox1.Text
'column you are checking
    CheckColumn = 2
    
    c = 1
'check for duplicate entery
    If Not Application.CountIf(y.Columns(CheckColumn), CheckDuplicate) > 1 Then
'get next blank row
    x = y.Range("B" & y.Rows.Count).End(xlUp).Row + 1
'loop each textbox control
    For i = 1 To 48
'column counter
        c = c + 1
'if column no > 10 then every odd no add 1 to column counter
        If i > 10 And i Mod 2 = 1 Then c = c + 1
'enter textbox value to range
        y.Cells(x, c).Value = Me.Controls("TextBox" & i).Text
    Next i
    Else
'duplicate - inform user
        MsgBox CheckDuplicate & Chr(10) & "Duplicate Entry", 48, "Duplicate"
    End If
End Sub

Dave

 

[lovejanu4u]

Thanks Dave
for your kind help
your support is much appreciated.

 

[dmt32]

Thanks Dave
for your kind help
your support is much appreciated.

Most welcome - glad suggestion helped you.

many thanks for feedback

Dave