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]

Do you think the code is not being triggered? Or are you getting an error from the code?

 

[Rick Rothstein]

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
  
[COLOR=#ff0000][B]  <<<< I think you should be doing something here >>>>[/B][/COLOR]

  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?

I changed the indentation and spacing on the code you posted so things are easier to follow and, after doing so, it looks like you are not doing anything in your code. Above the location I marked in red is code to validate your Target's value and below it begins the close down of your procedure (plus the GoTo labels and their code), but there is no code at the marked location that does anything with the Target's value.

 

[Chris Macro]

I think the point of the code was to put the changed cell value through a series of tests and if it passed all those tests, then the code would not do anything. If it failed a test, then it would output one of the message boxes and clear the value that was entered onto the spreadsheet.

 

[Rick Rothstein]

I think the point of the code was to put the changed cell value through a series of tests and if it passed all those tests, then the code would not do anything. If it failed a test, then it would output one of the message boxes and clear the value that was entered onto the spreadsheet.

That may well be the case, but if it is, then I do not see any problem with the code... with the possible exception of the OP having mishandled the worksheet's Protection scheme, the posted code works fine for me.

 

[datapig1]

I am the author of the workbook and I've created everything from scratch, including the protection scheme. Good news, though, I restarted at an earlier saved version and the code runs fine now. The funny thing is that the code is exactly the same, so I'm still clueless as to what was causing it to not work earlier.

That may well be the case, but if it is, then I do not see any problem with the code... with the possible exception of the OP having mishandled the worksheet's Protection scheme, the posted code works fine for me.

 

[datapig1]

It was not triggering at all. If you see my last post, it's working again now however. So I'll just leave it for now

Do you think the code is not being triggered? Or are you getting an error from the code?

 

[Chris Macro]

I'm assuming somehow your EnableEvents was turned off. If that ever happens again you can restart Excel or run this line of code to get it turned back on:

Sub Enable_Events()

Application.EnableEvents = True

End Sub

 

[datapig1]

Brilliant! I'll keep that for later!

 

[Rick Rothstein]

I'm assuming somehow your EnableEvents was turned off. If that ever happens again you can restart Excel or run this line of code to get it turned back on:

Sub Enable_Events()

Application.EnableEvents = True

End Sub

Just to follow up on Chris' excellent deduction... it is not necessary to house that statement in macro form... you can simply execute the code line

Application.EnableEvents = True

directly in the Immediate Window anytime.