Prevent duplicate entries in excel vba

If you want to prevent entering duplicate values in a column in Excel using VBA , you can refer to this article. First create a data entry userform. So that you need a commandbutton to transfer the data from userform to worksheet. Now double click on this command button and write thefollowing code. I used here countif formula to avoid entering duplicate values. countif function helps you to count the values based on your TexTbox's value. Use a if function where you can apply your condition. Here I applied if the value is greate than 0 that mean values are already exist then It will show by a massage box "Duplicate Item!".

In a commadbutton_click

Dim sht as worksheet 

Set sht = thisworkbook.sheets("sheet1")

 If application.worksheetfunction.countif(sht.range("A:A"), me.textbox1.value)> 0 then

Msgbox " Duplicate items exists"

Exit Sub

End if

or

Dim i As Long
Dim sh As Worksheet
Set sh = Sheets("Sheet1")

i = sh.Range("A" & Rows.Count).End(xlUp).Row + 1

' find duplicate
If Application.WorksheetFunction.CountIf(sh.Range("B" & 2, "B" & i), Me.TextBox2.Value) > 0  Then
MsgBox "Duplicate Value"
Exit Sub
End If