Phone Number Data Validation

[reberryjr]

I need some help with some data validation. I have a form that populates with data from a worksheet. Part of this data is a phone number. I've tried the code below, but I can't seem to get it to work.

What I want:
If the value in txt_Phone is numeric and 10 digits, the value gets formatted as (###)###-####.
If the value is 13 characters long, the sub exits, as that would be the phone number that loaded into the form.
Any other condition would present the msg box.

What I'm getting:
I can type in a 10 digit number that converts, but I get the msg box when I click the command button that evaluates everything.
If I leave the value that loaded into the text box (formatted as (###)###-####), I still get the msg box.

If IsNumeric(Me.txt_Phone.Value) And Len(Me.txt_Phone.Value) = 10 Then    Me.txt_Phone = Format(Me.txt_Phone.Value, "(###)###-####")
If Len(Me.txt_Phone.Value) = 13 Then Exit Sub
Else
    MsgBox "Please enter a valid phone number for the Client, using numbers only."
    If response = vbOK Then Me.txt_Phone.SetFocus
    Exit Sub
End If

Can anyone offer some advice on this please?

 

[Rick Rothstein]

I need some help with some data validation. I have a form that populates with data from a worksheet. Part of this data is a phone number. I've tried the code below, but I can't seem to get it to work.

What I want:
If the value in txt_Phone is numeric and 10 digits, the value gets formatted as (###)###-####.
If the value is 13 characters long, the sub exits, as that would be the phone number that loaded into the form.
Any other condition would present the msg box.

What I'm getting:
I can type in a 10 digit number that converts, but I get the msg box when I click the command button that evaluates everything.
If I leave the value that loaded into the text box (formatted as (###)###-####), I still get the msg box.

If IsNumeric(Me.txt_Phone.Value) And Len(Me.txt_Phone.Value) = 10 Then    Me.txt_Phone = Format(Me.txt_Phone.Value, "(###)###-####")
If Len(Me.txt_Phone.Value) = 13 Then Exit Sub
Else
    MsgBox "Please enter a valid phone number for the Client, using numbers only."
    If response = vbOK Then Me.txt_Phone.SetFocus
    Exit Sub
End If

Can anyone offer some advice on this please?

Not sure if this is totally what is wrong, but you cannot use single line If..Then syntax with an Else..EndIf block structure. Try it like this where I have had to guess at the logical flow that you were trying for. Also note that I change your If tests to make them more robust.

[table="width: 500"]
[tr]
	[td]If Me.txt_Phone.Value Like "##########" Then
    Me.txt_Phone = Format(Me.txt_Phone.Value, "(###)###-####")
ElseIf Me.txt_Phone.Value Like "#############" Then
    Exit Sub
Else
    MsgBox "Please enter a valid phone number for the Client, using numbers only."
    If response = vbOK Then
        Me.txt_Phone.SetFocus
        Exit Sub
    End If
End If[/td]
[/tr]
[/table]

 

[reberryjr]

I deployed your suggestion, but the msg box persists. I'm not sure, but I'm thinking that I might have to somehow account for the parenthesis and the dash within the field.

 

[Rick Rothstein]

I deployed your suggestion, but the msg box persists. I'm not sure, but I'm thinking that I might have to somehow account for the parenthesis and the dash within the field.

What do you mean by what I highlighted above... I thought you were entering either 10 or 13 digits and nothing else?

 

[reberryjr]

The form pulls in the contents of an existing worksheet, which will have a phone number already formatted as (###)###-####. There are 3 possible scenarios:
1. That phone number doesn't change. I want the code to recognize this as a valid entry, and not throw an error.
2. The phone number changed and the EU enters 10 digits. I want the code to throw an error if the entry is not 10 digits. If it is 10 digits, the code should format the phone number, and not throw an error.
3. If the EU deletes the phone number, the code should throw an error.

 

[Rick Rothstein]

The form pulls in the contents of an existing worksheet, which will have a phone number already formatted as (###)###-####. There are 3 possible scenarios:
1. That phone number doesn't change. I want the code to recognize this as a valid entry, and not throw an error.
2. The phone number changed and the EU enters 10 digits. I want the code to throw an error if the entry is not 10 digits. If it is 10 digits, the code should format the phone number, and not throw an error.
3. If the EU deletes the phone number, the code should throw an error.

Okay, I had misunderstood what you wanted. I can change the code for you, no problem, but I am unclear on what #2 means. I live in the US, so I am not sure what three digits to put in front of the ten digits when the number is only ten digits long. Can you clarify exactly how you wanted the ten digits to be expanded to thirteen digits?

 

[reberryjr]

The 13 characters are the formatted telephone number. 10 digit phone number, plus 2 for the parenthesis and 1 for the dash. The parenthesis and the dash mean I can't use IsNumeric, and I don't want to use string, because I don't want alpha characters in there.

 

[Rick Rothstein]

The 13 characters are the formatted telephone number. 10 digit phone number, plus 2 for the parenthesis and 1 for the dash. The parenthesis and the dash mean I can't use IsNumeric, and I don't want to use string, because I don't want alpha characters in there.

I think you missed the point of my question... what two digits should the code put inside the parentheses and what digit should the code put before the dash? Also, can you show me exactly what a 13-digit formatted phone number looks like so that I can know its exact pattern shape?

 

[reberryjr]

Let's say my phone number is 555-123-4567. Without the dashes, that's 10 digits. I want that formatted as (555)123-4567. So, the numbers are 10 digits. The open and close parenthesis are 2, and the dash is 1, totaling 13 characters. Sorry if I'm being dense in my explanations, and I appreciate the assistance!

 

[Rick Rothstein]

Let's say my phone number is 555-123-4567.

I believe that is the first time you have told us the value you are parsing has dashes in it.

Without the dashes, that's 10 digits. I want that formatted as (555)123-4567. So, the numbers are 10 digits. The open and close parenthesis are 2, and the dash is 1, totaling 13 characters. Sorry if I'm being dense in my explanations, and I appreciate the assistance!

I think I understand now. I think something like this should works then...

If Me.txt_Phone.Value Like "###-###-####" Then
    Me.txt_Phone = Format(Replace(Me.txt_Phone.Value, "-", ""), "(###)###-####")
ElseIf Me.txt_Phone.Value Like "(###)####-######" Then
    Exit Sub
Else
    MsgBox "Please enter a valid phone number for the Client, using numbers only."
    If response = vbOK Then
        Me.txt_Phone.SetFocus
        Exit Sub
    End If
End If