check duplicate and length of number in specific column

[mazan2010]

good afternoon all
can someone advice me how can add to my code

that not allow to save data if there is some any duplication in data in column E in Sheet10 or value in length less or more than 10 digit
and highlight this value that allow to edit it then can save file again

[Vraag en antwoord]

You forgot to place the sample file. So just a guess

Please Login or Register  to view this content.

[mazan2010]

I post sample file with three button
one for check duplication (its work)
second for check length (not work well )
third that want to mix between two code that prevent save before make correction for duplication and length

[Vraag en antwoord]

Doesn't it just work? Small adjustment

Please Login or Register  to view this content.

[mazan2010]

It does not work
please kindly find file with correct length (10 number) and no duplication

also code in secod buuton doesnot work , can check please for me what is wrong in this code , it used for length check and highlight incorrect data with yellow and give how many cell incorrect

[oeldere]

just in case you also want a formula solution:

G2 =IF(LEN(E2)=10=TRUE,"correct","")

H2 =IF(COUNTIF($E2:$E$50,$E2)>1,"duplicated","no dup.")

See the attached file.

[mazan2010]

Thank you so much dear oeldere for your idea
but what about vba code
even correct code in my post number 5 for my case to detect incorrect length

[oeldere]

I have not enough noledge of VBA to help you with your problem.

[mazan2010]

thank you so much , hope someone expert VBA can solve this for me

[protonLeah]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[mazan2010]

@ protonLeah

Great work , thank you so much its work very well

[mazan2010]

@protonLeah
Dear protonLeah
first , I apologize for this
can check attached file , because after add code to original data file , not work well like in attached file
I try to see what is wrong , but can't

[protonLeah]

I don't know what "...doesn't work well..." means

Put the global variables:
Option Explicit
Public DupCount As Long
Public BadLen As Long
Public DupCheck As Boolean
Public LenCheck As Boolean
in a standard module (Module1)

Modified duplicate check for case of no duplicates found:

Please Login or Register  to view this content.

modified length check for case of empty cells

Please Login or Register  to view this content.

[mazan2010]

@protonleah
Dear Protonleah
thank you so much for you improve the code to check how many cells is empty also, its so great

as in my attached, I only need check column E, Not F and G