allow phone numbers with spaces on a user form

[tony.reynolds]

Hello all,

I have a user form that have a phone and a fax field that i dont want letters entered.

i have in my code

If Not IsNumeric(Phone.Value) Then
MsgBox "Phone number not valid"
Exit Sub
End If

But dosnt allow number with spaces
ideally the number format should only allow XX XXXX XXXX format

can someone help me write my code ?

 

[Richard Schollar]

Hi

Perhaps try something like this:

Dim strPhoneNo As String
Dim dblPhoneNo As Double
 
strPhoneNo = Me.TextBox1.Value  'or whatever
 
strPhoneNo = Replace(strPhoneNo," ","")
 
On Error Resume Next
dblPhoneNo = CDbl(strPhoneNo)
On Error Goto 0
 
If dblPhoneNo <> 0 Then
  'phone number OK!
Else
  'phone number not in correct format!
End if

 

[Peter_SSs]

ideally the number format should only allow XX XXXX XXXX format

Possibly this?

With CreateObject("VBScript.RegExp")
    .Pattern = "\d{2} \d{4} \d{4}"
    If Not (Len(Phone.Value) = 12 And .Test(Phone.Value)) Then
        MsgBox "Phone number not valid"
    End If
End With

 

[Richard Schollar]

Come to think of it you could just do it like:

strPhoneNo = Me.Textbox1.Value 'or whatever
 
If strPhoneNo Like "## #### ####" Then
   'phone number OK!
Else
  'phone number not OK :(
End if

 

[tony.reynolds]

thanks ill try that...

Is Len a function of testing length?

Just trying to understand more

 

[tony.reynolds]

does ##...etc only alow numbers?

 

[Richard Schollar]

Yes - when using Like it denotes digits (0-9) only.

 

[Peter_SSs]

Is Len a function of testing length?

Yes.

 

[tony.reynolds]

Come to think of it you could just do it like:

strPhoneNo = Me.Textbox1.Value 'or whatever
 
If strPhoneNo Like "## #### ####" Then
   'phone number OK!
Else
  'phone number not OK :(
End if

That works really well! thanks for your really helpful code ideas