Making sure users enter phone numbers in a particular format

[Amber_D_Laws]

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

[Don Guillett]

tell em to do it or just use a custom format
###-###-####

--
Don Guillett
SalesAid Software
[email protected]
"Amber_D_Laws" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[tony h]

what format is required?
I have looked at the article and it seems a bit of a kludge

regards

[ForSale]

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.

[Tom Ogilvy]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[tony h]

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

[Tom Ogilvy]

No one suggested that one shouldn't design an application to fit their
needs/particular situation.

--
Regards.
Tom Ogilvy


"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Amber_D_Laws]

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

[Cutter]

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 is selected, go to the "Input Message" Tab, check the box for "show input 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 you want for the error window which will pop up if the user fails to input 10 digits

Click OK

[Tom Ogilvy]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[GS]

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

[Amber_D_Laws]

Thanks everybody....especially Tom!

The code Tom provided worked like a charm, and thanks to everybody else's comments I was confident it was going to do exactly what I needed it to.

The formatting I was refering to was in the cell, not the text box, so I figured that to restrict the text box to numerials only would make it possible for Excel to utilize the custom formating of the cell once the data from the text box was sent to the cell. However, it looks like from Tom's code this isn't nessecary at all! Hurray!

I really appreciate everything everyone has written. Getting all the differnet perspectives helped me not only solve my problem, but learn some new things too.

Cheers everybody! This was pleasantly painless.
Amber