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

"JacyErdelt" skrev i meddelelsen
...
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.

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.

--
Rick (MVP - Excel)


"Per Jessen" wrote in message
...
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

"JacyErdelt" skrev i meddelelsen
...
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.

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.



"Per Jessen" wrote:

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

"JacyErdelt" skrev i meddelelsen
...
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.

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)?

--
Rick (MVP - Excel)


"JacyErdelt" wrote in message
...
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.



"Per Jessen" wrote:

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

"JacyErdelt" skrev i meddelelsen
...
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.

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" skrev i meddelelsen
...
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.



"Per Jessen" wrote:

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

"JacyErdelt" skrev i meddelelsen
...
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.

It is in a Userform called frmEnterData.

"Rick Rothstein" wrote:

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)?

--
Rick (MVP - Excel)


"JacyErdelt" wrote in message
...
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.



"Per Jessen" wrote:

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

"JacyErdelt" skrev i meddelelsen
...
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.

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.

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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.



"Per Jessen" wrote:

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

"JacyErdelt" skrev i meddelelsen
...
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.

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" wrote in message
...
It is in a Userform called frmEnterData.

"Rick Rothstein" wrote:

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)?

--
Rick (MVP - Excel)


"JacyErdelt" wrote in message
...
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.



"Per Jessen" wrote:

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

"JacyErdelt" skrev i
meddelelsen
...
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

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

On 6 Apr., 02:55, JacyErdelt
wrote:
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.



"Per Jessen" wrote:
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" skrev i meddelelsen
...
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.

"Per Jessen" wrote:

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

"JacyErdelt" skrev i meddelelsen
...
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.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

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" wrote:

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" wrote in message
...
It is in a Userform called frmEnterData.

"Rick Rothstein" wrote:

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)?

--
Rick (MVP - Excel)


"JacyErdelt" wrote in message
...
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.



"Per Jessen" wrote:

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

"JacyErdelt" skrev i
meddelelsen
...
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.

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

"Rick Rothstein" wrote:

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" wrote in message
...
It is in a Userform called frmEnterData.

"Rick Rothstein" wrote:

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)?

--
Rick (MVP - Excel)


"JacyErdelt" wrote in message
...
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.



"Per Jessen" wrote:

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

"JacyErdelt" skrev i
meddelelsen
...
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.