Format a textbox

  • Thread starter Thread starter JacyErdelt
  • Start date Start date
J

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.
 
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
 
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.
 
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.
 
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)?
 
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
 
It is in a Userform called frmEnterData.

Rick Rothstein said:
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)?
Click to expand...
 
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.
 
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 ***************
 
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
 
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?

Rick Rothstein said:
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)


JacyErdelt said:
It is in a Userform called frmEnterData.
Click to expand...
Click to expand...
 
I meant MSForms, not mso. That was a different error altogether. Hope I
didn't confuse you.

Rick Rothstein said:
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)


JacyErdelt said:
It is in a Userform called frmEnterData.
Click to expand...
Click to expand...
 
Back
Top