formating a vba userform textbox

[vestax20]

Using Excel XP

Hi, I have a userform that contains a textbox. Other textboxes will update themselves using data entered to this textbox so i have to make sure the format is good.

Lets say I want this format

aaaa99999999

how would I go about doing this. I want four characters and eight numbers. Is there a way to do this with VBA.

Thx in advance

 

[Muzzery]

Try this for the on Exit command of the Text Box:

If Len(Me.TextBox) <> 12 Then
MsgBox &quot;The value must be 12 characters long&quot;
Me.TextBox.SetFocus
Exit Sub
ElseIf IsNumeric(Left(4, Me.TextBox)) Then
MsgBox &quot;The first 4 characters must be letters&quot;
Me.TextBox.SetFocus
Exit Sub
ElseIf Not IsNumeric(Right(8, Me.TextBox)) Then
MsgBox &quot;The last 8 characters must be numbers&quot;
Me.TextBox.SetFocus
Exit Sub
End If

That should do the trick.

 

[RobBroekhuis]

Depending on how you want to handle this, you could even check the format after every keystroke by the user (in the _change event), and refuse inappropriate ones. Or you could split the code into two textboxes, and automatically jump from the first to the second when four alpha characters have been added.

Rob

 

[senators40]

When I tried to enter this into the
Private Sub TextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)

or the command button click I get the following error

Run-time error '13' Type mismatch

When I debug it goes to the first ElseIf statement. Note the first if statement works and provides me with the correct error message. I am using Excel 97.

Thanks,

Jeff

 

[xlbo]

try this:

ElseIf IsNumeric(val(Left(4, Me.TextBox))) Then

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[RobBroekhuis]

Er, the order of the arguments to the left function simply needs to be swapped:

ElseIf IsNumeric(Left(TextBox,4)) Then

(don't need the Me qualifier either, within the userform code page)



Rob

 

[xlbo]

d'oh - Just keeping you on your toes Rob !!

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[senators40]

When I put &quot;Val&quot; in front of the left (letter elseIf) as RobBroekhuis and xlbo describe it never allows the program to proceed - it always stops at that procedure even if there are 4 letters and 8 numbers

Jeff

 

[RobBroekhuis]

Actually, I didn't include the val() (since val() is a numeric function, IsNumeric(val(..)) will always return TRUE). Try it without the val.


Rob