|
Format a textbox
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. |
Format a textbox
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. |
Format a textbox
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. |
Format a textbox
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. |
Format a textbox
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. |
Format a textbox
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. |
Format a textbox
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. |
Format a textbox
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. |
Format a textbox
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. |
Format a textbox
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 - |
Format a textbox
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. |
Format a textbox
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. |
| All times are GMT +1. The time now is 05:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com