Phone Number Validation

[austin350s10]

I have been working on a script to check if a user have entered a valid phone number into an InputBox. If they enter invalid information then it should keep looping until they enter valid information. Right now the following script only loops twice and lets invalid information pass through.

Any suggestions on how to fix this?

PNumber = Application.InputBox("Please enter the best phone number to contact you at", "Required", Type:=2)
    
    On Error GoTo ErrorHandler
    If PNumber Like "###-###-####" Then
    MsgBox "Stop"
    Else
ErrorHandler:
    PNumber = Application.InputBox("You didn't enter your phone number correctly" & vbNewLine _
    & "Please re-enter the best phone number to contact you at" & vbNewLine & vbNewLine _
    & "The correct format is ###-###-####", "Required", PNumber, Type:=2)
    End If
Resume Next

 

[poolhall]

why can't you just use Data Validation - Text Length - Equal to... 10. And then format the cell ###-###-####

?

 

[austin350s10]

unfortunately this is just a small part of a much larger script so it needs to be do using only VBA

 

[shg]

Maybe ...

    Dim sPhon       As String
 
    sPhon = InputBox(Prompt:="Please enter the best phone number to contact you", _
                     Title:="Required")
    If Len(sPhon) = 0 Then Exit Sub  
    sPhon = Replace(Replace(Replace(Replace(sPhon, "(", ""), ")", ""), "-", ""), " ", "")
 
    Do While Not IsNumeric(sPhon) Or Len(sPhon) <> 10
        sPhon = InputBox(Prompt:="Invalid, try again", _
                         Title:="Oops!")
        If Len(sPhon) = 0 Then Exit Sub 
        sPhon = Replace(Replace(Replace(Replace(sPhon, "(", ""), ")", ""), "-", ""), " ", "")
    Loop
 
    MsgBox "Good job!"
    ' carry on