mask with "??:??00"

[active_x]

example:

the data to be entered MUST BE in the format like "AB:??00",
e.g. AB:BB00, AB:CI28, AB:FK03...

"AB:" (two alphabets and a colon) is fixed

"??00" (two alphabets and two digits) is entered by users


Questions:

(Question 1)how to set the mask :
when user enter "??00", the cell is masked with "AB:??00"?

(Question 2)Should I use "mask" or "data validation"?

(Question 3)how to set in each case?

 

[Dave Peterson]

I think I'd use an event macro. It could either add the AB: or check to make
sure it's there. And while it's checking that, it can check the other stuff,
too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim invalidEntry As Boolean
Dim myStr As String

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a1:a10")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
invalidEntry = True
myStr = StrConv(Target.Value, vbUpperCase)

Select Case Len(myStr)
Case Is = 4
If Last4Ok(myStr) Then
Target.Value = "AB:" & myStr
invalidEntry = False
End If

Case Is = 7
If Left(myStr, 3) = "AB:" _
And Last4Ok(Right(myStr, 4)) Then
'make sure uppercase
Target.Value = myStr
invalidEntry = False
End If
End Select

If invalidEntry Then
Application.Undo
MsgBox "Please use a valid entry like: AB:XY00"
End If

errHandler:
Application.EnableEvents = True

End Sub

Function Last4Ok(str As String) As Boolean
If Left(str, 2) Like "[A-Z][A-Z]" _
And IsNumeric(Right(str, 2)) Then
Last4Ok = True
Else
Last4Ok = False
End If
End Function

I looked in A1:A10. Modify that range to match your data.

to install this: Right click on the worksheet tab that should have this
behavior. Select view code and paste this in.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm