Data validation 3 uppercase + 3 numbers

D

datapig1

New Member
Joined
Feb 26, 2014
Messages
28
Hello,

I need to prohibit anything typed into the range O5:O500, except for this format (vehicle registration numbers):

AAA111

Within the range there can't be any duplicates either, so in my example, if AAA111 is in cell O5, then the same AAA111 can't be used in O6:O500.

I've created a macro which changes lowercase letters to upper, which works, but I need to add the other functions as well. The code below doesn't need to be present, I reckon more efficient integrated codes works better.

Code: 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)


    If Intersect(Target, Range("O5:O500")) Is Nothing Then


    Else


    With Target


    If UCase(.Text) <> .Text Then
    .Value = UCase(.Text)


    End If


    End With


    End If


End Sub

Any suggestions?

Greatful for help on this!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
This should get you most of the way there. I'm still working on how to validate your required text format. Let me know if I'm on track!

<font face=Calibri><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> Excel.Range)<br>  <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>  <SPAN style="color:#00007F">Dim</SPAN> WordCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <br>  <SPAN style="color:#00007F">Set</SPAN> rng = Range("O5:O500")<br><br>  <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#007F00">'Make sure text is UpperCase</SPAN><br>      Target.Value = UCase(Target.Text)<br>    <SPAN style="color:#007F00">'Make sure text is not a duplicate</SPAN><br>      WordCount = Application.WorksheetFunction.CountIf(rng, Target.Value)<br>      <SPAN style="color:#00007F">If</SPAN> WordCount > 1 <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Duplicate Value Entered!"<br>        Target.Clear<br>        Target.Select<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>      <br>  <SPAN style="color:#00007F">End</SPAN> If<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
I think this should take care of all your requirements:

<font face=Calibri><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> Excel.Range)<br>  <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>  <SPAN style="color:#00007F">Dim</SPAN> WordCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <br>  Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>  <br>  <SPAN style="color:#00007F">Set</SPAN> rng = Range("O5:O500")<br><br>  <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#007F00">'Make sure text is UpperCase</SPAN><br>      Target.Value = UCase(Target.Text)<br>    <br>    <SPAN style="color:#007F00">'Make sure text is not a duplicate</SPAN><br>      WordCount = Application.WorksheetFunction.CountIf(rng, Target.Value)<br>      <SPAN style="color:#00007F">If</SPAN> WordCount > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> InvalidEntry<br><br>    <SPAN style="color:#007F00">'Make sure entry is in the right format</SPAN><br>      <SPAN style="color:#007F00">'Test Length</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Len(Target.Value) > 6 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> InvalidEntry<br><br>      <SPAN style="color:#007F00">'Last 3 chars numeric?</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsNumeric(Right(Target.Value, 3)) <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> InvalidEntry<br>  <br>      <SPAN style="color:#007F00">'Any Numbers in First 3 chars?</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> x = 0 <SPAN style="color:#00007F">To</SPAN> 9<br>          <SPAN style="color:#00007F">If</SPAN> InStr(1, Left(Target.Value, 3), x) <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> InvalidEntry<br>        <SPAN style="color:#00007F">Next</SPAN> x<br>  <SPAN style="color:#00007F">End</SPAN> If<br><br><SPAN style="color:#00007F">Set</SPAN> rng = <SPAN style="color:#00007F">Nothing</SPAN><br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br>DuplicateEntry:<br>  MsgBox "Duplicate Value Entered!"<br>  Target.Clear<br>  Target.Select<br>  <SPAN style="color:#00007F">Set</SPAN> rng = <SPAN style="color:#00007F">Nothing</SPAN><br>  Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>  <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br>InvalidEntry:<br>  MsgBox "Valid entries can only be 6 charactres in the format AAA###"<br>  Target.Clear<br>  Target.Select<br>  <SPAN style="color:#00007F">Set</SPAN> rng = <SPAN style="color:#00007F">Nothing</SPAN><br>  Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>  <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Chris Macro said:
I think this should take care of all your requirements:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim WordCount As Long

Application.EnableEvents = False

Set rng = Range("O5:O500")

If Not Intersect(Target, rng) Is Nothing Then
'Make sure text is UpperCase
Target.Value = UCase(Target.Text)

'Make sure text is not a duplicate
WordCount = Application.WorksheetFunction.CountIf(rng, Target.Value)
If WordCount > 1 Then GoTo InvalidEntry

'Make sure entry is in the right format
'Test Length
If Len(Target.Value) > 6 Then GoTo InvalidEntry

'Last 3 chars numeric?
If Not IsNumeric(Right(Target.Value, 3)) Then GoTo InvalidEntry

'Any Numbers in First 3 chars?
For x = 0 To 9
If InStr(1, Left(Target.Value, 3), x) Then GoTo InvalidEntry
Next x
End If

Set rng = Nothing
Application.EnableEvents = True
Exit Sub

DuplicateEntry:
MsgBox "Duplicate Value Entered!"
Target.Clear
Target.Select
Set rng = Nothing
Application.EnableEvents = True
Exit Sub

InvalidEntry:
MsgBox "Valid entries can only be 6 charactres in the format AAA###"
Target.Clear
Target.Select
Set rng = Nothing
Application.EnableEvents = True
Exit Sub

End Sub
Click to expand...
I would simplify the part of your code I highlighted in red to this...

Code: 
  If Not Intersect(Target, rng) Is Nothing Then
    If Not Target.Value Like "[A-Z][A-Z][A-Z]###" Then GoTo InvalidEntry
  End If
 
Upvote 0
Awesome Rick. I was trying to wrap my head around how I could do that! I knew there had to be a way :)
 
Upvote 0
No problem, glad we could help. And hey, I learned something new too!
 
Upvote 0
Rick Rothstein said:
I would simplify the part of your code I highlighted in red to this...

Code: 
  If Not Intersect(Target, rng) Is Nothing Then
    If Not Target.Value Like "[A-Z][A-Z][A-Z]###" Then GoTo InvalidEntry
  End If
Click to expand...


Rick, do you know if there is any way to accomplish this sort of verification through a spreadsheet data validation? For those who don't want to use VBA?
 
Upvote 0
Chris Macro said:
Rick, do you know if there is any way to accomplish this sort of verification through a spreadsheet data validation? For those who don't want to use VBA?
Click to expand...
No, the only thing I can think of to see if text matches a pattern involves array constants and those are not allowed in Validation formulas.

If you are interested in seeing the formula I was thinking of, then check this link out... http://www.mrexcel.com/forum/excel-questions/719296-check-string-cell.html#post3543996
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Userform to worksheet values followed by 3 digits
Replies
2
Views
185
ipbr21054
I
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
84
ipbr21054
I
C
  • Solved
ENSURE UPPERCASE STATUS AFTER ENTERING DATA
Replies
3
Views
687
Akuini
Akuini
M
  • Solved
Preventing Duplicated Data Input from Click Button from one sheet to another
Replies
7
Views
495
dmt32
D
A
  • Question
Sense checking UserFrom code
Replies
0
Views
273
AndyEd
A

Forum statistics

Threads
1,214,784
Messages
6,121,536
Members
449,037
Latest member
tmmotairi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top