Stephen has attended:
Excel VBA Intro Intermediate course
Excel VBA User Form Text Data entry Mask
Hi There
I’m working on a userform but need to add a layer of validation so that the input of data is in the following letter/number combination: AAAAA0000A
I know how to code for the length of the textbox, but how would I validate the combination above? Is this even possible?
This is what I have for the 10 digit validation that I have in place at the moment,
If Len(Me.portcode.Value) <> 10 Then
MsgBox "Portfolio Code is not 10 characters.", vbExclamation, "Payments & Transfers Error"
Me.portcode.SetFocus
Exit Sub
Any help is greatly appreciated,
RE: Excel VBA User Form Text Data entry Mask
Hi Stephen
Thanks for getting in touch. Excel VBA really lacks this Access-style input mask feature. I have an idea in code which will work but is really clumsy and can almost certainly can be improved.
If Not (WorksheetFunction.IsText(Left(Me.portcode.Value, 1))) Or _
Not (WorksheetFunction.IsText(Left(Me.portcode.Value, 2))) Or _
Not (WorksheetFunction.IsText(Left(Me.portcode.Value, 3))) Or _
Not (WorksheetFunction.IsText(Left(Me.portcode.Value, 4))) Or _
Not (WorksheetFunction.IsText(Left(Me.portcode.Value, 5))) Or _
Not (IsNumeric(Left(Me.portcode.Value, 6))) Or _
Not (IsNumeric(Left(Me.portcode.Value, 7))) Or _
Not (IsNumeric(Left(Me.portcode.Value, 8))) Or _
Not (IsNumeric(Left(Me.portcode.Value, 9))) Or _
Not (WorksheetFunction.IsText(Left(Me.portcode.Value, 10))) Then
MsgBox "Error"
End If
As I said, fairly clumsy and I'm sure a more elegant solution could be found but the principle is there.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
