Data validation 3 uppercase + 3 numbers

[datapig1]

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.

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!

 

[Chris Macro]

whew, that formula is ugly. It's unfortunate Data Validation can't do tasks like that!

 

[armik1986]

you can add this formula to column B.(or any other place)

=AND(AND(AND(AND(CODE(MID(A1,{4,5,6},1))>47,CODE(MID(A1,{4,5,6},1))<58),AND(CODE(MID(A1,{1,2,3},1))>64,CODE(MID(A1,{1,2,3},1))<91)),COUNTIF(A:A,A1)=1),LEN(A1)=6)

then use it as validation rule for column A.

1. paste the formula in cell b1.
2. populate the formula to B500
3.select A1:A500
4. in the data validation choose custom method and select B1 As the formula .(in case you select A500 to A1 the active cell should be A500. so press enter once to make A1 active. or use B500 as validating cell.)

thats all...

 

[datapig1]

I've ran into a problem. The whole sheet is protected except for some specific cells, for example O5:O500. So when the code runs - if an invalid entry has been made, the cell changes color and turns into a protected cell.

I guess it's the part where it says target clear that has to be changed maybe.

Is it possible to keep the formatting as it was before the input, even though the entry is wrong?

 

[datapig1]

This could also work, however, the sheet shall be exported as csv to another application, which is very sensitive to what type of cells that can be included. All the other columns have other values and formulas that can't be changed, as it is now.

you can add this formula to column B.(or any other place)

=AND(AND(AND(AND(CODE(MID(A1,{4,5,6},1))>47,CODE(MID(A1,{4,5,6},1))<58),AND(CODE(MID(A1,{1,2,3},1))>64,CODE(MID(A1,{1,2,3},1))<91)),COUNTIF(A:A,A1)=1),LEN(A1)=6)

then use it as validation rule for column A.

1. paste the formula in cell b1.
2. populate the formula to B500
3.select A1:A500
4. in the data validation choose custom method and select B1 As the formula .(in case you select A500 to A1 the active cell should be A500. so press enter once to make A1 active. or use B500 as validating cell.)

thats all...

 

[datapig1]

Furthermore, if you delete the entry, the same thing happens. As in, it's registered as an invalid entry and clears the formatting.

 

[armik1986]

This could also work, however, the sheet shall be exported as csv to another application, which is very sensitive to what type of cells that can be included. All the other columns have other values and formulas that can't be changed, as it is now.

unfortunately excel has limitation of 255 characters in formula box in the data validation. so you can not break the array down to a normal formula to use in data validation.(it would take 324 chars)....
buttry the following trick....
1. instead of Col B use the column to the end of your sheet and do as do as previous post.
2. select that column and press ctrl+1.
3. in the custom section type ;;;
that would be three semicolons.
this column would not be exported to your csv file...

 

[datapig1]

I changed the part clear, to clearcontents instead. Now the whole macro works perfectly.

Thank you all for your contributions!

I'm still very novice, but I'm learning, step by step.

 

[datapig1]

Your suggestion probably works equally as good, but since the workbook already is enabled for macros, it doesn't matter if I use VBA for this one.

I'll bookmark for later, though.

Thanks!

unfortunately excel has limitation of 255 characters in formula box in the data validation. so you can not break the array down to a normal formula to use in data validation.(it would take 324 chars)....
buttry the following trick....
1. instead of Col B use the column to the end of your sheet and do as do as previous post.
2. select that column and press ctrl+1.
3. in the custom section type ;;;
that would be three semicolons.
this column would not be exported to your csv file...

 

[Chris Macro]

I changed the part clear, to clearcontents instead. Now the whole macro works perfectly.

Thank you all for your contributions!

I'm still very novice, but I'm learning, step by step.

You're welcome! Glad you were able to get your solution!

 

[datapig1]

For some strange reason, the code does not work anymore.

I did the last changes quite late yesterday, so it's likely I messed something up before coming back to work.

In any case, this is the full code at the moment.

Option Explicit


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")


WordCount = Application.WorksheetFunction.CountIf(rng, Target.Value)
    If WordCount > 1 Then GoTo DuplicateEntry


    If Not Intersect(Target, rng) Is Nothing Then
    If Not Target.Value Like "[A-Z][A-Z][A-Z]###" Then GoTo InvalidEntry
End If
  
    Set rng = Nothing
    Application.EnableEvents = True
Exit Sub


DuplicateEntry:
    MsgBox "This vehicle registration number has already been used in another payment"
    Target.ClearContents
    Target.Select
    Set rng = Nothing
    Application.EnableEvents = True
Exit Sub


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


End Sub

Anything that looks peculiar?

The worksheet is protected except for specific cells, as mentioned. Otherwhise it's just data validation, vlookups and a couple of other VBA codes that's present.