In my macro, a form displays a textbox, radio buttons, and
push buttons. The textbox is populated with a phone
number. Sometimes the user will keep the existing number
(which is already formatted) and sometimes they will
change it.

I use the following code to format the textbox:

Private Sub TextBox3_Exit(ByVal cancel As
MSForms.ReturnBoolean)

If Not IsNumeric(TextBox3) Or Len(TextBox3) 10 Then
cancel = True
Me.TextBox3.Text = Format(TextBox3, "000-000-0000")

End Sub

I would like the users to be able to tab through the
form. The problem is they can tab through it until they
get to the text box. The tab is locked until they key in
a phone number.

What I would like to do is let the user tab through the
phone number if they keep the existing number and not
change it. If they change it and keep in 10 new number,
then it should be formatted automatically for them like it
does now. Any suggestions on how we can do this? Thanks
for the help.

Maybe...

Option Explicit
Private Sub TextBox3_Exit(ByVal cancel As MSForms.ReturnBoolean)

If (Me.TextBox3.Value Like "###-###-####") Then
'ok, do nothing
ElseIf Not IsNumeric(Me.TextBox3.Value) _
Or Len(Me.TextBox3.Value) 10 Then
cancel = True
End If

Me.TextBox3.Value = Format(me.TextBox3.value, "000-000-0000")

End Sub

(I like the me. and .value)



JT wrote:

In my macro, a form displays a textbox, radio buttons, and
push buttons. The textbox is populated with a phone
number. Sometimes the user will keep the existing number
(which is already formatted) and sometimes they will
change it.

I use the following code to format the textbox:

Private Sub TextBox3_Exit(ByVal cancel As
MSForms.ReturnBoolean)

If Not IsNumeric(TextBox3) Or Len(TextBox3) 10 Then
cancel = True
Me.TextBox3.Text = Format(TextBox3, "000-000-0000")

End Sub

I would like the users to be able to tab through the
form. The problem is they can tab through it until they
get to the text box. The tab is locked until they key in
a phone number.

What I would like to do is let the user tab through the
phone number if they keep the existing number and not
change it. If they change it and keep in 10 new number,
then it should be formatted automatically for them like it
does now. Any suggestions on how we can do this? Thanks
for the help.

--

Dave Peterson