Using data validation to control text length w/Vlookup

[Questionasker]

Can anyone provide a formula to be used in data validation that will control text length (6 digits) and restrict duplicate entries. The best formula will prevent anything other that 6 digits, but question the user regarding a duplicate entry.
For example: if the user enters 123456 no problem, but if 12345 is entered, Excel validation would not allow. If the user enters 123456 again, Excel's validation window would allow but the window will pop-up and ask to confirm.

Thank you,
Jon

 

[SteveO59L]

The length part, can be done with standard data validation.


You will probably need some VBA to do the unique part. Are you ok with that ?

 

[Peter_SSs]

Can anyone provide a formula to be used in data validation that will control text length (6 digits) and restrict duplicate entries. The best formula will prevent anything other that 6 digits, but question the user regarding a duplicate entry.
For example: if the user enters 123456 no problem, but if 12345 is entered, Excel validation would not allow. If the user enters 123456 again, Excel's validation window would allow but the window will pop-up and ask to confirm.

Thank you,
Jon

Standard Data Validation will not be able to do what you ask. It cannot absolutely allow some things (eg whole number with 6 digits), absolutely disallow others (eg a text entry, or number with other than 6 digits) and allow some others with warning (eg repeated 6 digit number). You would have to either ...

a) Consider a vba solution, or

b) Use Data Validation that doesn't quite meet your need.

Here is some Data Validation that warns if any of the following are entered:
- a non-numeric value
- a numeric, non integer value
- an integer with other than 6 digits
- a duplicate value

On the 'Error Alert' tab it has the 'Style' set to 'Warning', and I imagine an error message something like 'Enter a whole number from 100000 to 999999'

Excel Workbook
	A	B
1
2
DV vba

#VALUE!

 

[Questionasker]

Steve,
Thanks for the reply. If VBA is the solution, then yes I would be ok with that.

Jon

 

[Questionasker]

Peter,
Thanks for the reply. I used the formula with no success, however, when I used the exact same formula on a new spreadsheet, it worked perfectly. I even used the same cell location with success on the new, no problem. When I try the same formula on my current sheet, it gives an validation error even though the entry is 6 digits, with no repeat.
It could be a glitch on my end.
Any thoughts?
Jon

 

[Peter_SSs]

Peter,
Thanks for the reply. I used the formula with no success, however, when I used the exact same formula on a new spreadsheet, it worked perfectly. I even used the same cell location with success on the new, no problem. When I try the same formula on my current sheet, it gives an validation error even though the entry is 6 digits, with no repeat.
It could be a glitch on my end.
Any thoughts?
Jon

If the cell was formatted as 'Text' and then 6 digits entered, that would trigger the validation error. Could that be the case?

Just thinking about a possible vba approach...

Is the user likely to be entering only digits into the column (I assume it is a column? Which column? Does the column have a heading in row 1?). That is, does any error checking need to determine if text is entered and reject that? Does it need to check for decimal values and reject those?

 

[Questionasker]

Hello Peter,
Yes, the cell was formatted as text, good call.

Regarding the VBA approach, the range is in a column (F14:F33) with a header (F10). I have 20 rows in column F, the users will always enter a 6 digit "Supplier Detail Number" (F14:F33), no text. I want to prevent the user from entering anything less that 6 digit numbers or more that 6 digit numbers. The numbers do not have decimals, comma's, etc. For example: 123456, 235489, etc. So if a user entered 123.56, I would want Excel to prevent the entry.
I also want Excel to question the user if they enter duplicate numbers. Duplicate numbers are allowed, but rare. So I must allow them, but request verification from the user. I hope my explaination helps, if not let me know.

Again, thanks Peter for your time,
Jon

 

[Peter_SSs]

Jon

Try this Worksheet_Change code. I have assumed the user will only be entering a single value at a time. To implement ...

1. Right click the sheet name tab and choose "View Code".

2. Copy the code below and Paste into the main right hand pane that opened at step 1.

3. Close the VB window and test your sheet.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> myVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> anError <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, aWarning <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Resp <SPAN style="color:#00007F">As</SPAN> VbMsgBoxResult<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Range("F14:F33")<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, myRange) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        myVal = Target.Value<br>        <br>        <SPAN style="color:#00007F">If</SPAN> IsNumeric(myVal) <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Int(myVal) <> myVal <SPAN style="color:#00007F">Or</SPAN> myVal > 999999 <SPAN style="color:#00007F">Or</SPAN> myVal < 100000 <SPAN style="color:#00007F">Then</SPAN><br>                anError = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">Else</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> WorksheetFunction.CountIf(myRange, myVal) > 1 <SPAN style="color:#00007F">Then</SPAN><br>                    aWarning = <SPAN style="color:#00007F">True</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            anError = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN> anError <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Target<br>                .ClearContents<br>                .Select<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            MsgBox myVal & " is an invalid entry." & vbLf & "Enter a 6 digit number."<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN> aWarning <SPAN style="color:#00007F">Then</SPAN><br>            Resp = MsgBox(myVal & " is a duplicate, sure you want to keep it?", vbYesNo)<br>            <SPAN style="color:#00007F">If</SPAN> Resp = vbNo <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> Target<br>                    .ClearContents<br>                    .Select<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Questionasker]

Hello Peter,

Sorry for the belated reply, but I have not had a chance to work on my spreadsheet since your last post. Thank you for the code, however, i cannot seem to get it to work. Am I supposed to remove any data validation if I use this code?
I tried the code without data validation and nothing happened. Currently I have the last code pasted in the sheet tab and the data validation below.

=AND(ISNUMBER(E14),E14=INT(E14),LEN(E14)=6,COUNTIF(E:E,E14)=1)

Please tell me what I am missing, etc.

Thanks a million for your time and expertise!

Jon

 

[Peter_SSs]

Jon

See if any of this helps.

1. If using the code you would not have the Data Validation directly in the cell.

2. The code should be pasted in the particular Worksheet module. It sounds like you have that and if you followed steps 1 and 2 in my previous post it should be in the right place.

3. Check that you don't have macros disabled (Tools|Macro|Security...). If set to high, change it to Medium (or Low, though you need to be aware of the risks with that setting), save and close the workbook. Re-open the workbook (enable macros if prompted) and try again.

4. It is possible that the code is not working because 'Events' (eg Worksheet_Change) are disabled. In the VB window (Alt+F11 to get there), ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter

Now go back to your sheet and try changing a cell in the target range.

5. Don't forget that the target range for the code as posted is F14:F33 so that is where you have to be entering the values for the code to check.

6. The values need to be entered in the target range manually, not by formula.