Is it possible to retrict users to enter only 11 digits, no spaces and - in txt boxes

[wedd]

CALLING ALL VBA EXPERTS, WHIZZKIDS, PROFESSORS, TUTORS, MASTERS, EXPERIENCED GURUS and LEGENDS!!!! Is it possible to retrict users to enter only 11 numeric digits beginning with 0, with no spaces allowed, as well restrict the user from typing in (dashes) like (-) in the txt box using vba; that also displays an error message: "Invalid Phone number: Re-enter 11 digit Code beginning with 0"? If the user violates against the conditions.

I've enclosed the following code I used for this interesting problem...maybe you have better and effiecient workable code than this. I am a bit of a novice with vba, so if you have sample codes that can perform these requirements without bugs that will be appreciated.


Thanks for your contributions, advise and help I value it very highly


Private Sub Enter_Phone_Number_LostFocus()
'''''Description - Restricts the user from entering no more than 11 digits with spaces and - (dashes) within the text field
'''''Date - 16 April 2011
'''''APW
Dim strText As String, iPos As Integer
strText = TextBox17.Text
If Len(strText) = 11 And Left(strText, 1) = 0 Then
For iPos = 1 To Len(strText)
If IsNumeric(Left(strText, iPos)) Then
'do whatever you do if entry correct
Else
GoTo Error
End If
Next iPos
Else: GoTo Error
Error: MsgBox "Invalid Phone number: Re-enter 11 digit Code beginning with 0"
TextBox17.Activate
End If
strText = vbNullString
Case 48 To 57 'numbers 0 To 11
If Len(TextBox1.Text) = 0 Then
If KeyAscii <> 56 Then KeyAscii = 0 ' must start with 0
End If
End Sub

[orange]

Try this, but watch for line breaks when you copy the code

I have set up a Function that will return Pass or Fail depending on whether the passed string meets the Valid criteria or not.

I have included a tester program.
Change the values of myphone to show invalid and valid values.

You will have to adjust your Event code to call the function, if you choose to use it.

'---------------------------------------------------------------------------------------
' Procedure : ValidatePhone
' Author    : Jack
' Created   : 4/13/2011
' Purpose   : This routine will check an input string against a pattern.
'To be valid the input
'         must be all numerics
'         must start with 0 and
'         must be 11 chars long.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs:  a string
' Dependency: Requires a reference to Microsoft VBScript Regular Exressions
'------------------------------------------------------------------------------
'
Function ValidatePhone(sPhone As String) As String
   On Error GoTo ValidatePhone_Error

  With CreateObject("vbscript.regexp")
        .Pattern = "(^0[0-9]{10})"
        .Global = True
     If .test(sPhone) Then
         ValidatePhone = "Pass"
     Else
         ValidatePhone = "Fail"
     End If
    End With

   On Error GoTo 0
   Exit Function

ValidatePhone_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ValidatePhone of Module AWF_Related"
End Function


Sub testValidatePhone()
'no message if the myphone is valid
Dim myPhone As String
myPhone = "01978481234"
 If ValidatePhone(myPhone) <> "Pass" Then
   MsgBox myPhone & " is not a valid phone number"
 End If
End Sub

[wedd]

Thanks Orange! I appreciate it. I will test it.

[orange]

wedd,

I didn't put a NULL check in the function for sPhone. My understanding is that you would do that before calling the ValidatePhone routine.

Put this in the function for completeness.
It goes before the line With CreateObject
and after On Error GoTo ValidatePhone_Error

[VBA]If Len(sPhone & "") = 0 Then
ValidatePhone = "Fail"
Exit Function
End If[/VBA]

[BrianMH]

wouldn't it be easier to set an input mask such as "0"0000000000

[hansup]

I didn't put a NULL check in the function for sPhone. My understanding is that you would do that before calling the ValidatePhone routine.

A Null check in the function would be pointless. That function will never accept Null for sPhone because you declared it as String. And Null is not a String value. It would give you run-time error 94, Invalid use of Null

[VBA]Function ValidatePhone(sPhone As String) As String[/VBA]

[hansup]

Check for non-digit characters with simple pattern matching. Consider this example copied from the Immediate Window.

[VBA]? "01234567891" Like "*[!0-9]*"
False
? "a1234567891" Like "*[!0-9]*"
True[/VBA]

[orange]

wedd,

I didn't put a NULL check in the function for sPhone. My understanding is that you would do that before calling the ValidatePhone routine.

Put this in the function for completeness.
It goes before the line With CreateObject
and after On Error GoTo ValidatePhone_Error

[vba]If Len(sPhone & "") = 0 Then
ValidatePhone = "Fail"
Exit Function
End If[/vba]

hansup is correct, a NULL check would be pointless.

I mis-named this check. It's really checking for a zero length string.

I have adjusted the Tester program, so that whatever string you pass, a message will be written to the immediate window.
Here is the adjusted code. Test it.

[vba]
'---------------------------------------------------------------------------------------
' Procedure : ValidatePhone
' Author : Orange
' Created : 4/13/2011
' Purpose : This routine will check an input string against a pattern.
'To be valid the input
' must be all numerics
' must start with 0 and
' must be 11 chars long.
'---------------------------------------------------------------------------------------
' Last Modified: 4/14/2011 ** Check for ZLS in sPhone (hansup VBAX)
'
' Inputs: a string
' Outputs: "Pass" or "Fail"
' Dependency: Requires a reference to Microsoft VBScript Regular Exressions
'------------------------------------------------------------------------------
'
Function ValidatePhone(sPhone As String) As String

On Error GoTo ValidatePhone_Error

Check_for_ZEero_length_String:
If Len(sPhone & "") = 0 Then
ValidatePhone = "Fail"
Exit Function
End If

With CreateObject("vbscript.regexp")
.Pattern = "(^0[0-9]{10})"
.Global = True
If .test(sPhone) Then
ValidatePhone = "Pass"
Else
ValidatePhone = "Fail"
End If
End With

On Error GoTo 0
Exit Function

ValidatePhone_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ValidatePhone of Module AWF_Related"
End Function


Sub testValidatePhone()
'no message if the myphone is valid
'
'Fail if
' ZLS is passed or
' all chars not numeric, or
' first digit is not 0, or
' length is not 11
'
Dim myPhone As String
myPhone = "12978481234" ' change this value, or comment it out
myPhone = Nz(myPhone, "")
If Len(myPhone & "") = 0 Then
Debug.Print "Myphone has no Value"
Else
Debug.Print "Myphone has a Value of " & myPhone
End If

If ValidatePhone(myPhone) <> "Pass" Then
MsgBox myPhone & " is not a valid phone number"
End If
End Sub
[/vba]

[hansup]

[VBA]Function ValidatePhone(sPhone As String) As String

On Error GoTo ValidatePhone_Error

Check_for_ZEero_length_String:
If Len(sPhone & "") = 0 Then[/VBA]

Since sPhone is String type, it can never be Null. So there is no point in concatenating an empty string to it before measuring its length.
[VBA]If Len(sPhone) = 0 Then[/VBA]

[VBA]
Sub testValidatePhone()
'no message if the myphone is valid
'
'Fail if
' ZLS is passed or
' all chars not numeric, or
' first digit is not 0, or
' length is not 11
'
Dim myPhone As String
myPhone = "12978481234" ' change this value, or comment it out
myPhone = Nz(myPhone, "")
[/VBA]

myPhone was declared as String, so Nz() function not needed.

[VBA]
If Len(myPhone & "") = 0 Then
[/VBA]

[VBA]If Len(myPhone) = 0 Then[/VBA]

I think you're throwing too much code at a simple problem.

[VBA]Public Function IsValidPhoneNum(ByVal pString As String) As Boolean
IsValidPhoneNum = (Len(pString) = 11 _
And pString Like "0*" _
And Not pString Like "*[!0-9]*")
End Function[/VBA]

Test the function in the Immediate Window:
[VBA]? IsValidPhoneNum("01234967890")
True
? IsValidPhoneNum("91234967890")
False
? IsValidPhoneNum("0123")
False
? IsValidPhoneNum("012foo7890")
False
? IsValidPhoneNum("0123-967890")
False[/VBA]

Call the function from the After Update event of which ever control holds the phone numer ... maybe it's called TextBox17.

[VBA]If Not IsValidPhoneNum(Nz(Me.TextBox17,"")) Then[/VBA]

If there's an error with the IsValidPhoneNum() function, let the form code's error hander deal with it.

[geekgirlau]

Very nice Hansup - pattern matching is something I often forget about, but it provides a beautifully streamlined approach.

Wedd, I'd also suggest you get into the habit of assigned logical names for your controls. "Me.txtPhone" is much easier to decipher than "Me.TextBox17", particularly when you have multiple controls on a form.

[orange]

Hey hansup,

Very nice. And you're correct

"no point in concatenating an empty string to it before measuring its length."

I think you're throwing too much code at a simple problem.