Excel field input mask

[steve.waye]

Hi all,

I want users to enter the following into a field and have the entry rejected if the mask is not followed...

19ABC-001

So, any two digits (representing the year) followed by any three letters (that convert to UPPER CASE if not entered as such) followed by a hyphen (entered automatically if the user doesn't enter it) followed by any three digits.

I have tried using Format Cell as well as Data Validation with no success.

My VBA skills are quite elementary and are limited to self-taught scripts that I've found on Mr Excel and elsewhere.

Hoping there's a simple way to do this.

Thanks, Steve

 

[Scott Huish]

I have no idea, it worked for me.

 

[steve.waye]

OK. Thanks.

One last thing. The popup appears when the contents of a cell are deleted.

It would be good if this didn't happen as the message is not relevant for deleting the contents.

 

[Scott Huish]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, e As Range
Set d = Intersect(Range("J:J"), Target)
Set e = Intersect(Range("A:A"), Target)
Rem Proper Case
If Not e Is Nothing Then
    Application.EnableEvents = False
        e(1) = WorksheetFunction.Proper(e(1))
    Application.EnableEvents = True
End If
If d Is Nothing Then Exit Sub
Rem INPUT MASK ##AAA-###
Application.EnableEvents = False
For Each c In d
    c = UCase(c)
    If c Like "##[A-Z][A-Z][A-Z]###" Or c Like "##[A-Z][A-Z][A-Z]-###" Then
        If Mid(c, 6, 1) <> "-" Then c = Application.WorksheetFunction.Replace(c, 6, 0, "-")
    Else
        If c <> "" Then
            MsgBox "Cell " & c.Address(0, 0) & " is in invalid format"
            c.ClearContents
            c.Select
        End If
    End If
Next
Application.EnableEvents = True
End Sub

 

[steve.waye]

Works perfect. Thanks for your help.