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]

Try this. This is written for Column A, change as necessary. Copy the code below. Right click the tab where you want this to happen on and click on View Code. Paste into white area. Hit Alt-Q to close the Visual Basic Editor. Save your workbook as type .xlsm

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Rem INPUT MASK ##AAA-###
Set d = Intersect(Range("A:A"), Target)
If d Is Nothing Then Exit Sub
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
        MsgBox "Cell " & c.Address(0, 0) & " is in invalid format"
        c.ClearContents
        c.Select
    End If
Next
Application.EnableEvents = True
End Sub

 

[steve.waye]

Thanks for that.

A couple of issues when testing...

If I simply enter "19" it reverts to "00LLL-019" (i.e. it doesn't enforce the full requirements of the mask)
If I enter "19bel101" it doesn't change the bel to BEL (upper case) and doesn't automatically enter the hyphen.
If I enter "XY12" the alert box doesn't popup.

Any ideas?

 

[Scott Huish]

19 resulted in invalid format.
19bel101 changed to 19BEL-101
XY12 resulted in invalid format.

What did you change exactly?

 

[steve.waye]

Changed the Range in the script from A:A to J:J.

Then cut and pasted as usual.

 

[steve.waye]

Pasted into the relevant sheet code. Not a module.

I wouldn't expect that I have to close and reopen the workbook.

 

[steve.waye]

Pasted code...

I'm using MS Office Professional Plus

 

[Scott Huish]

You have 2 worksheet Change events. That's going to cause a problem. Give me a moment.

 

[Scott Huish]

Replace both those sections of code with this:

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
        MsgBox "Cell " & c.Address(0, 0) & " is in invalid format"
        c.ClearContents
        c.Select
    End If
Next
Application.EnableEvents = True
End Sub

 

[steve.waye]

Still didn't work, and the Proper Case script for column A stopped working as well.

I then closed the workbook, reopened, and now it works!

Is there a reason for this?