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 D=ref1,column E=ref2 until we get to column O which is ref12. the issue i have is i cant have the same reference number in any of these columns, i need a solution where all twelve textboxes are checked against all columns D to O. example below (this customer is only using one REF so columns E-O will be left blank, second example is a customer using 3 REF's but the REF code 123 has already been used, note that this REF might not be in the same column/textbox as before. this is why it is important that all columns are checked.

example 1
A-Customer name:toys 4 u
A-Customer name:toys 4 u (textbox1)
B-collection date:15/01/2023 (textbox2)
C-collection time: 09:00 (textbox3)
D-ref1:123 (textbox4)

example 2
A-Customer name:toys 4 u (textbox1)
B-collection date:15/01/2023 (textbox2)
C-collection time: 09:00 (textbox3)
D-ref1:456 (textbox4)
E-ref2:789 (Textbox5)
F-ref3:123 (textbox6)

as you can see in example 2, textbox6 =column F is now a duplicate because this was recorded in example 1 but in textbox4=column D.

i would also like a msgbox if a duplicate is found " Reference already exists, please use sequence e.g 123-1

pleaseeeeeeeeeeeeeeeeeeeeee help!!!!

the file has failed to upload. any advise?

sorry new to this

sorry to sound stupid, but how do i upload the whole file, when i did try it said file failed to upload

Originally Posted by torachan

sorry reluctant to open zip files - upload your actual file with the code and userform in it - desensitise any confidential data - it impossible to analyse pictures - we need to run the code in-situ to diagnose errors.

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,textbox30 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..

the below can be found on the userform image which is "REFERENCE" in a 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

Originally Posted by torachan

where is the uploaded workbook - 'crystal ball gazing' (aka "guessing") is a waste of time - I prefer to give a solution with one post if possible rather than waste time, at my time of life every second is precious.

it said the file was to big to send, i have made a copy with some data in, i hope this helps, if not is there a way that i can send you the full file

believe me i dont want to waste your time or anyones time here, i appreciate the help and hope you guys can solve this for me



test .xlsx

Originally Posted by torachan

you have uploaded a .xlsx file (no code, no form) - the file with the form/code has to be saved as a .xlsm file.

the file which has the userforms and tabs for the data to go to keeps failing, im guessing because of the size of the file.

is there another way of me to send this file to you?

Originally Posted by PLwolves87

the file which has the userforms and tabs for the data to go to keeps failing, im guessing because of the size of the file.

is there another way of me to send this file to you?

ive saved the file as the below
Microsoft Excel Macro-Enabled Worksheet (.xlsm)
3.36 MB (3,525,722 bytes)

so how do i do a sample? how can i make the file smaller?

ive sent a copy of what the spreadsheet would look like with all its columns, the only thing i cant upload with it is the userform because im guessing the amount of code makes the file to large.

cant i not just copy all of the code from my userform and then post that in a message?

Originally Posted by torachan

I have never seen a UserForm that large - it is most likely the data contained in the sheet - in your copy file cut it down to a couple of dozen rows.

im extremely confused nowm as my sheet has no data in it, all that is set up is the column headers.

ive done a zip file and now the file is 3116kb

Originally Posted by sintek

It is not going to help...We need to see your file...Code is written based on file setup...
See if you can zip the file and attach...I will open your zip file...
Also...Please explain in step by step detail what it is you are trying to accomplish...We are not mind readers...
Your code and images have no value if we do not understand what you are wanting to achieve...

i know your not mind readers and if i could upload the file i would have lol

this is what im trying to do
extbox29 - 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..

Originally Posted by sintek

Press Ctrl & Z on the sheet and see where the cursor ends up

Try this...Open your file and also a blank excel file...Then move Userform to blank file and save as some other name...Then check size of that file...

its gone to 2635kb

Originally Posted by sintek

You have already posted all your code...It is not going to help...All you are doing is cluttering this thread with nonsense..
We need the file...
Do as I suggested in Post 18

can i send the file via email?

Originally Posted by sintek

Press Ctrl & Z on the sheet and see where the cursor ends up

Try this...Open your file and also a blank excel file...Then move Userform to blank file and save as some other name...Then check size of that file...

the userform on its own is 17kb?

think i will need to remake the userform and then repost here as i have no idea why this is so big.

id like to thank you for taking a look and trying to help me. hopefully my next post will be the attached file