Userform which stops duplicates entering data sheet

[PLwolves87]

can someone please help me

textbox29 - textbox40 are recording reference numbers, but i cant have the same reference number appear in any of my columns once the data has been transferred. each textbox is on one multi page on my userform. so for example textbox29 has REF123 and this isnt already recorded this will enter, if i then add a new booking and textbox29 is REF456 and then textbox30 is REF123 this will flag as a duplicate as this was already recorded in a different column on the data enter sheet.

multi page.
textbox29 this data goes to columnD
textbox30 this data goes to columnE
textbox31 this data goes to columnF
textbox32 this data goes to columnG
textbox33 this data goes to columnH
textbox34 this data goes to columnI
textbox35 this data goes to columnJ
textbox36 this data goes to columnK
textbox37 this data goes to columnL
textbox38 this data goes to columnM
textbox39 this data goes to columnN
textbox40 this data goes to columnO

this is the code i have but will only look up textbox29 in column D,

'this code was added when i click the command button on the userform to insert the data to check for the duplicate

Private Sub MAKEBOOKING_Click()
Dim a As Long, x As Long
a = Application.WorksheetFunction.CountIf(Sheet1.Range("D:D"), Me.TextBox29.Text)
x = Application.WorksheetFunction.CountA(Sheet1.Range("D:D"))
If Me.TextBox29 <> "" And a = 0 Then
Sheet1.Range("a" & x).Value = Me.TextBox29.Text
Else

End If
End Sub



'this code was added aswell (unsure what this does apart from gthe msgbox)
Private Sub TextBox29_AfterUpdate()

Dim a As Long
a = Application.WorksheetFunction.CountIf(Sheet1.Range("D:D"), Me.TextBox29.Text)

If a >= 1 Then
TextBox29 = ""
MsgBox "REF number already Exist,Please check or add as sequence e.g 12345-1"
End If
End Sub




basically i have textbox29 to textbox40 but i need each textbox checked against each other

so textbox29 which is in columnD needs to be checked against column D,E,F,G,H,I,J,K,L,M,N,O and then textbox30 will do the same,and then textbox31 and so on..

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA userform_ preventing duplicate from different columns

Hi, please HELP, so i have set up the userform which is for my transport records. basically i have 40 columns worth of data (40 textboxes on my userform), columns D to O are reference numbers. for example column A =customer name, column B = collection date, column C = collection time, column...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.