Format a textbox

[JacyErdelt]

Could anyone tell me how I might be able to format a textbox to auto format
as a person is entering data. For instance when a person is entering the
phone number (555)555-5555, the 1st 3 digits wills automatically be place
within the parenthesis and the hypen will be placed when the next 3 digits
have been entered. I know i can format the box after it has LostFocus, but
all the time we use advanced stand-alone programs that can be setup to do it
as the user is entering info and I thought maybe VBE could do it too. If you
have any suggestions, let me know. Thank you.

 

[Per Jessen]

Hi

Sure it can be done.
In addition to this I would set the MaxLength (Properties) of the TextBox to
13 to limit the number of digits after the hypen to 4.

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If IsNumeric(Chr(KeyCode)) Then
If Len(Me.TextBox1) = 1 Then Me.TextBox1 = "(" & Me.TextBox1
If Len(Me.TextBox1) = 4 Then Me.TextBox1 = Me.TextBox1 & ")"
If Len(Me.TextBox1) = 8 Then Me.TextBox1 = Me.TextBox1 & "-"
Else
If Len(Me.TextBox1) <> 0 Then
Me.TextBox1 = Left(Me.TextBox1, Len(Me.TextBox1) - 1)
End If
End If
End Sub

Regards,
Per

 

[Rick Rothstein]

One caution to the OP... your solution doesn't allow for normal editing.
First off, the arrow keys erase numbers from the end. Second, try this...
type 4 numbers, click into the number part inside the parentheses and start
typing more numbers... the format is not preserved.

 

[JacyErdelt]

Thank you for your response. I am trying to insert your code into mine, but I
am having some problems. When I insert exactly what you wrote and then run
it, the code is not executed because it is not associated with that specific
textbox (which I think is expected because I assume "textbox1" is a generic
name). However when I change that to specifically refer to the textbox (which
is named "txtPhone") I get the following compile error; Procedure declaration
does not match description of event or procedure having the same name. This
is what the code looks like when I get the compile error;

Private Sub txtPhone_KeyUp(ByVal KeyCode As MSForms.ReturnInteger,
ByValShift As Integer)
If IsNumeric(Chr(KeyCode)) Then
If Len(Me.txtPhone) = 1 Then Me.txtPhone = "(" & Me.txtPhone
If Len(Me.txtPhone) = 4 Then Me.txtPhone = Me.txtPhone & ")"
If Len(Me.txtPhone) = 8 Then Me.txtPhone = Me.txtPhone & "-"
Else
If Len(Me.txtPhone) <> 0 Then
Me.txtPhone = Left(Me.txtPhone, Len(Me.txtPhone) - 1)
End If
End If
End Sub

If "textbox1" is not just a generic reference to the textbox, then you need
to know that I actually have 7 textboxes in my userform and the phone number
is 5th in the tab order, but I have no idea what number it was given when I
created it. Your help is greatly appreciated. Thank you.

 

[Rick Rothstein]

Where is the TextBox located (on the worksheet or on a User Form)? If on the
worksheet, where did you get it from (the Drawing or Control Toolbox
toolbar)?

 

[Per Jessen]

The problem is that you have removed the carriage return between ByVal and
Shift without inserting a space in the first statement.

It should look like this:

.....ByVal Shift As Integer)

Regards,
Per

 

[JacyErdelt]

It is in a Userform called frmEnterData.

Where is the TextBox located (on the worksheet or on a User Form)? If on the
worksheet, where did you get it from (the Drawing or Control Toolbox
toolbar)?

 

[JacyErdelt]

Yes.Thank you. That was what I wanted it to do. Only one problem now, when I
try to enter the last digit it doesn't let me. I can only get as far as
(555)555-555. Any ideas?

Also, since you are clearly an expert, how would I do the same thing for the
date, so there would be no chance of entering it in the incorrect format? I
would like it to auto format 04/01/09.

 

[Rick Rothstein]

Here is some code for you to test out. It would be best if you did that in a
new UserForm (not your current one) until you are sure it does what you
want; then you can incorporate it into your main UserForm. Place a TextBox
on the UserForm and name it txtPhone, then copy/paste all of the code below
into the UserForm's code window. That is it; now, run the UserForm and try
different types of entry and editing into the TextBox (try non-numbers,
deleting digits, and whatnot). I think the code's execution is clean and I
believe I caught and accounted for all the invalid operations a user might
do (of course, if you spot something I missed, let me know and I will try to
patch the code).

'*************** START OF CODE ***************
Dim LastPosition As Long

Private Sub txtPhone_Change()
Dim Cursor As Long
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
SecondTime = True
With txtPhone
If Left(.Text, 1) <> "(" Then .Text = "(" & .Text
If Mid(.Text, 2) Like "*[!0-9)-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
Cursor = .SelStart
Do While InStr(.Text, "-")
.SelStart = InStr(.Text, "-") - 1
.SelLength = 1
.SelText = ""
Cursor = Cursor - 1
Loop
Do While InStr(.Text, ")")
.SelStart = InStr(.Text, ")") - 1
.SelLength = 1
.SelText = ""
Cursor = Cursor - 1
Loop
If Len(.Text) > 4 Then
.SelStart = 4
.SelText = ")"
Cursor = Cursor + 1
End If
If Len(.Text) > 8 Then
.SelStart = 8
.SelText = "-"
Cursor = Cursor + 1
End If
.SelStart = IIf(Cursor < 0, 0, Cursor)
LastText = .Text
LastPosition = Cursor
End If
End With
SecondTime = False
End If
End Sub

Private Sub txtPhone_Enter()
With txtPhone
.MaxLength = 13
If Len(.Text) = 0 Then
.Text = "("
.SelStart = 1
End If
End With
End Sub

Private Sub txtPhone_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With txtPhone
LastPosition = .SelStart
End With
End Sub

Private Sub txtPhone_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
With txtPhone
If .SelStart = 0 Then
.SelStart = 1
End If
End With
End Sub

Private Sub txtPhone_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
With txtPhone
If .SelStart = 0 Then .SelStart = LastPosition
LastPosition = .SelStart
End With
End Sub
'*************** END OF CODE ***************

 

[Per Jessen]

Hi

It's working as desired here. Check if you have set the MaxLength
property to 12 rather than 13.

I see you have made another post regarding the date problem, so I
assume you have settled it.

Regards,
Per

 

[JacyErdelt]

It works great! But there is one problem that I hope will be easy to fix. I
forgot that while I am working from 2007 there will be people running the
program using Excel 2003. And when I try to run it in their version the
"mso"s cause compile errors. I'm sorry, you put so much effort into making
this code perfect for me, and I then I have to throw a wrench in the whole
thing. Do you know what I need to do to make it run in 2003?

Here is some code for you to test out. It would be best if you did that in a
new UserForm (not your current one) until you are sure it does what you
want; then you can incorporate it into your main UserForm. Place a TextBox
on the UserForm and name it txtPhone, then copy/paste all of the code below
into the UserForm's code window. That is it; now, run the UserForm and try
different types of entry and editing into the TextBox (try non-numbers,
deleting digits, and whatnot). I think the code's execution is clean and I
believe I caught and accounted for all the invalid operations a user might
do (of course, if you spot something I missed, let me know and I will try to
patch the code).

'*************** START OF CODE ***************
Dim LastPosition As Long

Private Sub txtPhone_Change()
Dim Cursor As Long
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
SecondTime = True
With txtPhone
If Left(.Text, 1) <> "(" Then .Text = "(" & .Text
If Mid(.Text, 2) Like "*[!0-9)-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
Cursor = .SelStart
Do While InStr(.Text, "-")
.SelStart = InStr(.Text, "-") - 1
.SelLength = 1
.SelText = ""
Cursor = Cursor - 1
Loop
Do While InStr(.Text, ")")
.SelStart = InStr(.Text, ")") - 1
.SelLength = 1
.SelText = ""
Cursor = Cursor - 1
Loop
If Len(.Text) > 4 Then
.SelStart = 4
.SelText = ")"
Cursor = Cursor + 1
End If
If Len(.Text) > 8 Then
.SelStart = 8
.SelText = "-"
Cursor = Cursor + 1
End If
.SelStart = IIf(Cursor < 0, 0, Cursor)
LastText = .Text
LastPosition = Cursor
End If
End With
SecondTime = False
End If
End Sub

Private Sub txtPhone_Enter()
With txtPhone
.MaxLength = 13
If Len(.Text) = 0 Then
.Text = "("
.SelStart = 1
End If
End With
End Sub

Private Sub txtPhone_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With txtPhone
LastPosition = .SelStart
End With
End Sub

Private Sub txtPhone_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
With txtPhone
If .SelStart = 0 Then
.SelStart = 1
End If
End With
End Sub

Private Sub txtPhone_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
With txtPhone
If .SelStart = 0 Then .SelStart = LastPosition
LastPosition = .SelStart
End With
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)

It is in a Userform called frmEnterData.

 

[JacyErdelt]

I meant MSForms, not mso. That was a different error altogether. Hope I
didn't confuse you.

Here is some code for you to test out. It would be best if you did that in a
new UserForm (not your current one) until you are sure it does what you
want; then you can incorporate it into your main UserForm. Place a TextBox
on the UserForm and name it txtPhone, then copy/paste all of the code below
into the UserForm's code window. That is it; now, run the UserForm and try
different types of entry and editing into the TextBox (try non-numbers,
deleting digits, and whatnot). I think the code's execution is clean and I
believe I caught and accounted for all the invalid operations a user might
do (of course, if you spot something I missed, let me know and I will try to
patch the code).

'*************** START OF CODE ***************
Dim LastPosition As Long

Private Sub txtPhone_Change()
Dim Cursor As Long
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
SecondTime = True
With txtPhone
If Left(.Text, 1) <> "(" Then .Text = "(" & .Text
If Mid(.Text, 2) Like "*[!0-9)-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
Cursor = .SelStart
Do While InStr(.Text, "-")
.SelStart = InStr(.Text, "-") - 1
.SelLength = 1
.SelText = ""
Cursor = Cursor - 1
Loop
Do While InStr(.Text, ")")
.SelStart = InStr(.Text, ")") - 1
.SelLength = 1
.SelText = ""
Cursor = Cursor - 1
Loop
If Len(.Text) > 4 Then
.SelStart = 4
.SelText = ")"
Cursor = Cursor + 1
End If
If Len(.Text) > 8 Then
.SelStart = 8
.SelText = "-"
Cursor = Cursor + 1
End If
.SelStart = IIf(Cursor < 0, 0, Cursor)
LastText = .Text
LastPosition = Cursor
End If
End With
SecondTime = False
End If
End Sub

Private Sub txtPhone_Enter()
With txtPhone
.MaxLength = 13
If Len(.Text) = 0 Then
.Text = "("
.SelStart = 1
End If
End With
End Sub

Private Sub txtPhone_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With txtPhone
LastPosition = .SelStart
End With
End Sub

Private Sub txtPhone_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
With txtPhone
If .SelStart = 0 Then
.SelStart = 1
End If
End With
End Sub

Private Sub txtPhone_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
With txtPhone
If .SelStart = 0 Then .SelStart = LastPosition
LastPosition = .SelStart
End With
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)

It is in a Userform called frmEnterData.