allow phone numbers with spaces on a user form

tony.reynolds

Board Regular
Joined
Jul 8, 2010
Messages
97
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 ? :confused:
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Perhaps try something like this:

Code:
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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,633
Office Version
365
Platform
Windows
ideally the number format should only allow XX XXXX XXXX format
Possibly this?
Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Come to think of it you could just do it like:

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

tony.reynolds

Board Regular
Joined
Jul 8, 2010
Messages
97
thanks ill try that...

Is Len a function of testing length?

Just trying to understand more
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Yes - when using Like it denotes digits (0-9) only.
 

tony.reynolds

Board Regular
Joined
Jul 8, 2010
Messages
97
Come to think of it you could just do it like:

Code:
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
 

Forum statistics

Threads
1,085,334
Messages
5,383,029
Members
401,812
Latest member
topherj09

Some videos you may like

This Week's Hot Topics

Top