|
Making sure users enter phone numbers in a particular format
I was doing some research on how to make my users enter phone numbers consistantly using the same format and I happened upon the following page, http://www.dotnet247.com/247referenc...51/257905.aspx which seems to be an archived and compiled selection from some forum or another. In the discussion they talked about using three text boxes, all with specified lengths and using "auto-focus" on them in succession then adding in the dashes and parenthises later programaticly. Sounds great! However, they don't ever say how to accomplish this. As far as I can tell it is a discussion between experienced programers who already know how, and they are just talking technique not actual code. Meanwhile, I am not an experienced coder and I don't know how. So far it has been the only intelligable anything I have drummed up on the subject. If anyone has any ideas as to how to actually code what three text boxes to take focus in succession, and then programaticly add parenthises and hyphens to the text as it is entered onto an Excel spread sheet, please let me know. Any help will be appreciated, even if it is just links to other relevant topics. Thanks, Amber;) -- Amber_D_Laws ------------------------------------------------------------------------ Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012 View this thread: http://www.excelforum.com/showthread...hreadid=509591 |
Making sure users enter phone numbers in a particular format
what format is required? I have looked at the article and it seems a bit of a kludge regards -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=509591 |
Making sure users enter phone numbers in a particular format
Just go to Format | Cells | Custom | and you can make your own custom format. You can do ###-###-#### or (###) ###-#### or any other format that you want. -- ForSale ------------------------------------------------------------------------ ForSale's Profile: http://www.excelforum.com/member.php...o&userid=11896 View this thread: http://www.excelforum.com/showthread...hreadid=509591 |
Making sure users enter phone numbers in a particular format
On a Userform?
Let them enter it how they want, then strip out everything but numbers and format it the way you want (or just use the number) Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim s as String, s1 as String, i as long, l as long ' strip out any characters s = Textbox1.Text s1 = "" for i = 1 to len(s) if isnumeric(mid(s,i,1)) then s1 = s1 & mid(s,i,1) end if Next if len(s1) < 10 then msgbox "Invalid Number" Textbox1.Text = "" Cancel = true else l = int(s1) s1 = format(l,"(###) ###-####") TextBox1.Text = s1 end if End Sub -- Regards, Tom Ogilvy "Amber_D_Laws" wrote in message news:Amber_D_Laws.22vsxy_1139353201.9895@excelforu m-nospam.com... I was doing some research on how to make my users enter phone numbers consistantly using the same format and I happened upon the following page, http://www.dotnet247.com/247referenc...51/257905.aspx which seems to be an archived and compiled selection from some forum or another. In the discussion they talked about using three text boxes, all with specified lengths and using "auto-focus" on them in succession then adding in the dashes and parenthises later programaticly. Sounds great! However, they don't ever say how to accomplish this. As far as I can tell it is a discussion between experienced programers who already know how, and they are just talking technique not actual code. Meanwhile, I am not an experienced coder and I don't know how. So far it has been the only intelligable anything I have drummed up on the subject. If anyone has any ideas as to how to actually code what three text boxes to take focus in succession, and then programaticly add parenthises and hyphens to the text as it is entered onto an Excel spread sheet, please let me know. Any help will be appreciated, even if it is just links to other relevant topics. Thanks, Amber;) -- Amber_D_Laws ------------------------------------------------------------------------ Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012 View this thread: http://www.excelforum.com/showthread...hreadid=509591 |
Making sure users enter phone numbers in a particular format
Just a caution on trying to make things too tight. Just looking at the format of telephone numbers I have - all of which are valid : USA +1 212 111 1111 01 212 111 1111 UK 01411 123456 +44 (0) 411 123456 0207 123 1234 0044 411 123456 France 0033 5 11 22 33 44 +33 5 11 22 33 44 05 11 22 33 44 Singapore 0 1234 5678 -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=509591 |
Making sure users enter phone numbers in a particular format
No one suggested that one shouldn't design an application to fit their
needs/particular situation. -- Regards. Tom Ogilvy "tony h" wrote in message ... Just a caution on trying to make things too tight. Just looking at the format of telephone numbers I have - all of which are valid : USA +1 212 111 1111 01 212 111 1111 UK 01411 123456 +44 (0) 411 123456 0207 123 1234 0044 411 123456 France 0033 5 11 22 33 44 +33 5 11 22 33 44 05 11 22 33 44 Singapore 0 1234 5678 -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=509591 |
Making sure users enter phone numbers in a particular format
Wow....that kind of stired up a bee hive. My users will not have to enter international numbers. In fact all of our clients are from the US within a 3-4 state region. So coding for disperate forms of country/area codes isn't really a consideration in this instance, but thanks for the heads-up.:) The format I want them to use is *(###) ###-####* So, as someone suggested, a custom format is easily accomplished, and restricting the text box to no more than 12 characters is also easy. What I don't know how to do is to limit what the user puts into the text box to only *numerials*, and how to restrict them to using no *less* than 12 characters. Keep the idea coming, I am really learning alot from the discussion. Regards, Amber -- Amber_D_Laws ------------------------------------------------------------------------ Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012 View this thread: http://www.excelforum.com/showthread...hreadid=509591 |
Making sure users enter phone numbers in a particular format
Use the custom format as described by ForSale in earlier reply To limit input in the cell use Data Validation Assuming you are using A1 for the phone number: Click on A1 Give it the custom format of (###) ###-#### Now click on Data Validation On the Settings Tab - For "Allow" choose Custom For the "Formula" type: =AND(ISNUMBER(A1),LEN(A1)=10) If you want instructions for the user to pop up when the cell i selected, go to the "Input Message" Tab, check the box for "show inpu message when cell is selected" and type in a Title and Message Click on the "Error Alert" Tab and give a title and message that yo want for the error window which will pop up if the user fails to inpu 10 digits Click O -- Cutte ----------------------------------------------------------------------- Cutter's Profile: http://www.excelforum.com/member.php...nfo&userid=984 View this thread: http://www.excelforum.com/showthread.php?threadid=50959 |
Making sure users enter phone numbers in a particular format
the format you show has 14 characters and 4 of them are not numerals. So
restricitng it to 12 numerals only would be a little frustrating for your users. the obvious answer is to use the change event (or keydown/up/press events) to check every character entered and reacting to it. The change event fires on every key stroke (as do the others). the exit event would make the check after the fact. I gave an example of that. -- Regards, Tom Ogilvy "Amber_D_Laws" wrote in message news:Amber_D_Laws.22x15n_1139410504.8372@excelforu m-nospam.com... Wow....that kind of stired up a bee hive. My users will not have to enter international numbers. In fact all of our clients are from the US within a 3-4 state region. So coding for disperate forms of country/area codes isn't really a consideration in this instance, but thanks for the heads-up.:) The format I want them to use is *(###) ###-####* So, as someone suggested, a custom format is easily accomplished, and restricting the text box to no more than 12 characters is also easy. What I don't know how to do is to limit what the user puts into the text box to only *numerials*, and how to restrict them to using no *less* than 12 characters. Keep the idea coming, I am really learning alot from the discussion. Regards, Amber -- Amber_D_Laws ------------------------------------------------------------------------ Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012 View this thread: http://www.excelforum.com/showthread...hreadid=509591 |
Making sure users enter phone numbers in a particular format
If you use Tom's code exactly as constructed, it will filter the user input
for numbers only. The test for length in the "If" block determines the number of characters in the final result. Just modify it (and the format string) to give you what you want. If the input isn't correct, it displays a message to the user. Otherwise, he gave you a "drop-in" solution. <BTW The format you posted here has 14 characters, consisting of 10 numbers, 2 parenthesis, 1 space, and 1 hyphen. In this case, Tom's code will do the job "as is". Possibly: 1. You could enter instructional info in the Tooltip property of that textbox to assist your users. This will display when the mouse is over the control. OR 2. You could add a label with instructional info in its Caption property. Regards, GS |
Making sure users enter phone numbers in a particular format
Thanks everybody....especially Tom! The code Tom provided worked like a charm, and thanks to everybod else's comments I was confident it was going to do exactly what needed it to. The formatting I was refering to was in the cell, not the text box, s I figured that to restrict the text box to numerials only would make i possible for Excel to utilize the custom formating of the cell once th data from the text box was sent to the cell. However, it looks lik from Tom's code this isn't nessecary at all! Hurray! I really appreciate everything everyone has written. Getting all th differnet perspectives helped me not only solve my problem, but lear some new things too. Cheers everybody! This was pleasantly painless. Amber: -- Amber_D_Law ----------------------------------------------------------------------- Amber_D_Laws's Profile: http://www.excelforum.com/member.php...fo&userid=3001 View this thread: http://www.excelforum.com/showthread.php?threadid=50959 |
| All times are GMT +1. The time now is 09:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com